pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedNested Loop join being improperly chosen
3+ messages / 2 participants
[nested] [flat]
* Nested Loop join being improperly chosen
@ 2008-08-22 15:26 Brad Ediger <brad@bradediger.com>
2008-08-28 23:01 ` Re: Nested Loop join being improperly chosen David Rowley <dgrowley@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Brad Ediger @ 2008-08-22 15:26 UTC (permalink / raw)
To: pgsql-performance
Hello,
I'm having trouble with a Nested Loop being selected for a rather
complex query; it turns out this is a pretty bad plan as the nested
loop's row estimates are quite off (1 estimated / 1207881 actual). If
I disable enable_nestloop, the query executes much faster (42 seconds
instead of 605). The tables in the query have all been ANALYZEd just
before generating these plans.
Here are the plans with and without enable_nestloop:
http://pastie.org/258043
The inventory table is huge; it currently has about 1.3 x 10^9 tuples.
The items table has around 10,000 tuples, and the other tables in the
query are tiny.
Any ideas or suggestions would be greatly appreciated. Thanks!
--
Brad Ediger
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Nested Loop join being improperly chosen
2008-08-22 15:26 Nested Loop join being improperly chosen Brad Ediger <brad@bradediger.com>
@ 2008-08-28 23:01 ` David Rowley <dgrowley@gmail.com>
2008-08-28 23:22 ` Re: Nested Loop join being improperly chosen Brad Ediger <brad@bradediger.com>
0 siblings, 1 reply; 3+ messages in thread
From: David Rowley @ 2008-08-28 23:01 UTC (permalink / raw)
To: 'Brad Ediger' <brad@bradediger.com>; +Cc: pgsql-performance
I had a similar problem here:
http://archives.postgresql.org/pgsql-bugs/2008-07/msg00026.php
Is the nested loop performing a LEFT join with yours? It's a little
difficult to tell just from the query plan you showed.
A work around for mine was to use a full outer join and eliminate the extra
rows in the where clause. A bit of a hack but it changed a 2 min query into
one that ran in under a second.
Of course this is not helping with your problem but at least may trigger
some more feedback.
David.
-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Brad Ediger
Sent: 22 August 2008 16:26
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Nested Loop join being improperly chosen
Hello,
I'm having trouble with a Nested Loop being selected for a rather
complex query; it turns out this is a pretty bad plan as the nested
loop's row estimates are quite off (1 estimated / 1207881 actual). If
I disable enable_nestloop, the query executes much faster (42 seconds
instead of 605). The tables in the query have all been ANALYZEd just
before generating these plans.
Here are the plans with and without enable_nestloop:
http://pastie.org/258043
The inventory table is huge; it currently has about 1.3 x 10^9 tuples.
The items table has around 10,000 tuples, and the other tables in the
query are tiny.
Any ideas or suggestions would be greatly appreciated. Thanks!
--
Brad Ediger
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Nested Loop join being improperly chosen
2008-08-22 15:26 Nested Loop join being improperly chosen Brad Ediger <brad@bradediger.com>
2008-08-28 23:01 ` Re: Nested Loop join being improperly chosen David Rowley <dgrowley@gmail.com>
@ 2008-08-28 23:22 ` Brad Ediger <brad@bradediger.com>
0 siblings, 0 replies; 3+ messages in thread
From: Brad Ediger @ 2008-08-28 23:22 UTC (permalink / raw)
To: David Rowley <dgrowley@gmail.com>; +Cc: pgsql-performance
On Aug 28, 2008, at 6:01 PM, David Rowley wrote:
> I had a similar problem here:
> http://archives.postgresql.org/pgsql-bugs/2008-07/msg00026.php
>
> Is the nested loop performing a LEFT join with yours? It's a little
> difficult to tell just from the query plan you showed.
>
> A work around for mine was to use a full outer join and eliminate
> the extra
> rows in the where clause. A bit of a hack but it changed a 2 min
> query into
> one that ran in under a second.
>
> Of course this is not helping with your problem but at least may
> trigger
> some more feedback.
Hi David,
Thanks for your input. All of the joins are inner joins; the query is
a large one with 5 or 6 subqueries. It was being generated from a
popular data warehousing / business intelligence product whose name I
shall not mention. The vendor ended up pulling the subselects out into
SELECT INTO statements on temporary tables. It's kludgey, but it works
much better.
Thanks,
Brad
Attachments:
smime.p7s (application/pkcs7-signature, 2.4K) download
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2008-08-28 23:22 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-08-22 15:26 Nested Loop join being improperly chosen Brad Ediger <brad@bradediger.com>
2008-08-28 23:01 ` David Rowley <dgrowley@gmail.com>
2008-08-28 23:22 ` Brad Ediger <brad@bradediger.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox