Hi,
It seems that a COPY of 8M rows to a table to 8.4rc1 takes >30% longer than
it does to 8.3.7 on Solaris.
Here are the steps I've taken to reproduce this problem on two different
solaris boxes (Solaris 10 11/06 s10x_u3wos_10 X86 and Solaris 10 8/07
s10x_u4wos_12b X86). I've tried this on a Li
On Tue, Jun 16, 2009 at 9:30 PM, Bryce Ewing wrote:
> Hi,
>
> I have been trying to fix a performance issue that we have which I have
> tracked down to index scans being done on a particular table (or set of
> tables):
>
> The following query:
> explain analyze select *
> FROM inbound.event_2009052
On Tue, Jun 16, 2009 at 11:16 AM, Alexander Staubo wrote:
> On Tue, Jun 16, 2009 at 4:36 PM, Tom Lane wrote:
>> Actually the easiest way to fix that is to get rid of the LIMIT.
>> (Maybe use a cursor instead, and fetch only twenty rows.) LIMIT
>> magnifies the risks from any estimation error, and
Aaron Turner writes:
> I know the costs are just relative, but I assumed
> cost=19229.08..29478.99 isn't 5 minutes of effort even on crappy
> hardware.
Very likely the bulk of the time is spent in the DELETE work proper,
not in the query to find the rows to be deleted. In particular I wonder
if
Hi,
I have been trying to fix a performance issue that we have which I have
tracked down to index scans being done on a particular table (or set of
tables):
The following query:
explain analyze select *
FROM inbound.event_20090526 e
LEFT OUTER JOIN inbound.internal_host i ON (e.mta_host_id =
On Tue, Jun 16, 2009 at 6:36 PM, Robert Haas wrote:
> On Tue, Jun 16, 2009 at 9:23 PM, Aaron Turner wrote:
>> DELETE FROM muapp.pcap_store AS x
>> USING muapp.pcap_store AS a
>> LEFT JOIN muapp.pcap_store_log b ON a.pcap_storeid =
>> b.pcap_storeid WHERE x.pcap_storeid = a.pcap_stor
On Tue, Jun 16, 2009 at 6:36 PM, Robert Haas wrote:
> On Tue, Jun 16, 2009 at 9:23 PM, Aaron Turner wrote:
>> DELETE FROM muapp.pcap_store AS x
>> USING muapp.pcap_store AS a
>> LEFT JOIN muapp.pcap_store_log b ON a.pcap_storeid =
>> b.pcap_storeid WHERE x.pcap_storeid = a.pcap_stor
On Tue, Jun 16, 2009 at 9:23 PM, Aaron Turner wrote:
> On Tue, Jun 16, 2009 at 5:30 PM, Robert Haas wrote:
>> On Tue, Jun 16, 2009 at 7:39 PM, Aaron Turner wrote:
>>> On Tue, Jun 16, 2009 at 2:37 PM, Alvaro
>>> Herrera wrote:
Aaron Turner escribió:
> I'm trying to figure out how to optimiz
On Tue, Jun 16, 2009 at 5:30 PM, Robert Haas wrote:
> On Tue, Jun 16, 2009 at 7:39 PM, Aaron Turner wrote:
>> On Tue, Jun 16, 2009 at 2:37 PM, Alvaro
>> Herrera wrote:
>>> Aaron Turner escribió:
I'm trying to figure out how to optimize this query (yes, I ran
vacuum/analyze):
mu
On Tue, Jun 16, 2009 at 7:39 PM, Aaron Turner wrote:
> On Tue, Jun 16, 2009 at 2:37 PM, Alvaro
> Herrera wrote:
>> Aaron Turner escribió:
>>> I'm trying to figure out how to optimize this query (yes, I ran
>>> vacuum/analyze):
>>>
>>> musecurity=# explain DELETE FROM muapp.pcap_store WHERE pcap_st
On Tue, Jun 16, 2009 at 2:37 PM, Alvaro
Herrera wrote:
> Aaron Turner escribió:
>> I'm trying to figure out how to optimize this query (yes, I ran
>> vacuum/analyze):
>>
>> musecurity=# explain DELETE FROM muapp.pcap_store WHERE pcap_storeid
>> NOT IN (SELECT pcap_storeid FROM muapp.pcap_store_log
Aaron Turner escribió:
> I'm trying to figure out how to optimize this query (yes, I ran
> vacuum/analyze):
>
> musecurity=# explain DELETE FROM muapp.pcap_store WHERE pcap_storeid
> NOT IN (SELECT pcap_storeid FROM muapp.pcap_store_log);
What PG version is this?
--
Alvaro Herrera
I'm trying to figure out how to optimize this query (yes, I ran vacuum/analyze):
musecurity=# explain DELETE FROM muapp.pcap_store WHERE pcap_storeid
NOT IN (SELECT pcap_storeid FROM muapp.pcap_store_log);
QUERY PLAN
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On the DB side of things, you will want to make sure that your caching
as much as possible - putting a front-end like memcached could help. I
assume you have indexes on the appropriate tables? What does the
EXPLAIN ANALYZE on that query look like?
Not necessarily a "postgres" solution, but I'd t
Good afternoon.
I have developed an application to efficiently schedule chemotherapy
patients at our hospital. The application takes into account several
resource constraints (available chairs, available nurses, nurse coverage
assignment to chairs) as well as the chair time and nursing time
requir
Even if the query end in aproximately 200 sec, the explain analyze is
still working and there are gone more than 1000 sec...
I leave it working this night.
Have a nice evening and thenks for the help.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes t
Unfortunatly the query need that level of complxity as the information I
have to show are spread around different table.
I have tryed the geqo on at the beginning but only with the default
parameters.
Tomorrow (my working day here in Italy is finished some minutes ago, so
I will wait for the end of
Alberto Dalmaso writes:
> Ok, but the problem is that my very long query performes quite well when
> it works with merge join but it cannot arrive to an end if it use other
> kind of joining.
> If i put all the parameter to on, as both of you tell me, in the
> explanation I'll see that the db use
Alberto Dalmaso wrote:
> I attach the explanation of the log query after setting all the
> enable to on. In this condition the query will never finish...
I notice that you many joins in there. If the query can't be
simplified, you probably need to boost the join_collapse_limit and
from_colla
Il giorno mar, 16/06/2009 alle 11.31 -0400, Tom Lane ha scritto:
> Alberto Dalmaso writes:
> > Il giorno mar, 16/06/2009 alle 15.58 +0100, Matthew Wakeling ha scritto:
> >>> enable_hashjoin = off
> >>> enable_nestloop = off
> >>> enable_seqscan = off
> >>> enable_sort = off
> >>
> >> Why are thes
Alberto Dalmaso wrote:
> do you thing it is impossible to find a
> configuration that works fine for both the kind of query?
No. We probably just need a little more information.
> The application have to run even versus oracle db... i wont have to
> write a different source for the two data
> Could you show us the result of SELECT version(); ?
of course I can
PostgreSQL 8.3.7 on x86_64-unknown-linux-gnu, compiled by GCC gcc (SUSE
Linux) 4.3.2 [gcc-4_3-branch revision 141291]
>
> Have you done any VACUUM VERBOSE lately and captured the output? If
> so, what do the last few lines sa
Alberto Dalmaso wrote:
> Il giorno mar, 16/06/2009 alle 15.58 +0100, Matthew Wakeling ha
> scritto:
>> On Tue, 16 Jun 2009, Alberto Dalmaso wrote:
>> > enable_hashjoin = off
>> > enable_nestloop = off
>> > enable_seqscan = off
>> > enable_sort = off
>>
>> Why are these switched off?
>>
> beca
Alberto Dalmaso writes:
> Il giorno mar, 16/06/2009 alle 15.58 +0100, Matthew Wakeling ha scritto:
>>> enable_hashjoin = off
>>> enable_nestloop = off
>>> enable_seqscan = off
>>> enable_sort = off
>>
>> Why are these switched off?
>>
> because of the need to pump up the performance of the compl
On Tue, Jun 16, 2009 at 4:36 PM, Tom Lane wrote:
> Actually the easiest way to fix that is to get rid of the LIMIT.
> (Maybe use a cursor instead, and fetch only twenty rows.) LIMIT
> magnifies the risks from any estimation error, and you've got a lot
> of that here ...
There's no cursor support
Il giorno mar, 16/06/2009 alle 15.58 +0100, Matthew Wakeling ha scritto:
> On Tue, 16 Jun 2009, Alberto Dalmaso wrote:
> >> What does your postgresql.conf file look like?
>
> > enable_hashjoin = off
> > enable_nestloop = off
> > enable_seqscan = off
> > enable_sort = off
>
> Why are these switche
Alberto Dalmaso wrote:
>> What version of PostgreSQL?
> 8.3 that comes with opensuse 11.1
Could you show us the result of SELECT version(); ?
> max_prepared_transactions = 30
Unless you're using distributed transactions or need a lot of locks,
that's just going to waste some RAM. Zero i
On Tue, 16 Jun 2009, Alberto Dalmaso wrote:
What does your postgresql.conf file look like?
enable_hashjoin = off
enable_nestloop = off
enable_seqscan = off
enable_sort = off
Why are these switched off?
and that is the explain of the too slow simple query
"Merge Join (cost=0.00..1032305.5
> What version of PostgreSQL?
8.3 that comes with opensuse 11.1
>
> What OS?
Linux, opensuse 11.1 64 bit
>
> What does the hardware look like? (CPUs, drives, memory, etc.)
2 * opteron dual core 8 GB RAM, 70 GB SCSI U320 RAID 1
>
> Do you have autovacuum running? What other regular maintenan
Alexander Staubo writes:
> Here's the query:
> select photos.*
> from photos
> inner join event_participations on
> event_participations.user_id = photos.creator_id and
> event_participations.attend = true
> inner join event_instances on
> event_instances.id = event_participations.event_inst
Alberto Dalmaso wrote:
> I have complex query that perform very well with mergejoin on and
> nestloop off.
> If I activate nestloop postgres try to use it and the query
> execution become inconclusive: after 3 hours still no answare so I
> kill the query.
> Tht's ok but, with this configuration
On Tue, Jun 16, 2009 at 03:37:42PM +0200, Alberto Dalmaso wrote:
> Hi everybody, I'm creating my database on postgres and after some days
> of hard work I'm arrived to obtain good performance and owfull
> performace with the same configuration.
> I have complex query that perform very well with mer
On Tue, Jun 16, 2009 at 3:56 PM, Dave Dutcher wrote:
>> -Original Message-
>> From: Alexander Staubo
>>
>> -> Nested Loop (cost=0.00..5729774.95 rows=10420 width=116)
>> (actual time=262614.470..262614.470 rows=0 loops=1)
>> Join Filter: ((photos.taken_at > (event_instances."t
> -Original Message-
> From: Alexander Staubo
>
>-> Nested Loop (cost=0.00..5729774.95 rows=10420 width=116)
> (actual time=262614.470..262614.470 rows=0 loops=1)
> Join Filter: ((photos.taken_at > (event_instances."time" +
> '-01:00:00'::interval)) AND (photos.taken_at < (e
Hi everybody, I'm creating my database on postgres and after some days
of hard work I'm arrived to obtain good performance and owfull
performace with the same configuration.
I have complex query that perform very well with mergejoin on and
nestloop off.
If I activate nestloop postgres try to use it
Here's the query:
select photos.*
from photos
inner join event_participations on
event_participations.user_id = photos.creator_id and
event_participations.attend = true
inner join event_instances on
event_instances.id = event_participations.event_instance_id
where (
(event_instances.venue_
37 matches
Mail list logo
