pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuestion about subselect/IN performance
4+ messages / 3 participants
[nested] [flat]
* Question about subselect/IN performance
@ 2010-11-30 17:43 T.H. <calinet6@gmail.com>
2010-11-30 22:54 ` Re: Question about subselect/IN performance Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 4+ messages in thread
From: T.H. @ 2010-11-30 17:43 UTC (permalink / raw)
To: pgsql-performance
I have a query that's running an IN/Subselect that joins three different
tables and gets a list of IDs to compare against... the subselect
basically looks for records through a join table based on the 3rd
table's name, similar to:
... IN (SELECT id FROM foo, foo_bar, bar
WHERE foo.id = foo_bar.foo_id
AND bar.id = foo_bar.bar_id
AND bar.name = "something") ...
This is all nested in a fairly complex query, and several of these
subselects operate on different tables within the query. The whole
thing, on some high-cardinality cases, can take 2.5 seconds to run
(clearly something can be done about that).
So in this example, the cardinality of the bar table is very low, and
fairly constant, something on the order of 5-7 records. In an
optimization attempt, I reduced the joins in the subselect from 2 to 1
by passing in the ID of the bar with the correct name, which I can
easily cache application-side or pre-fetch in a single query. Now it
looks like this:
... IN (SELECT id FROM foo, foo_bar
WHERE foo.id = foo_bar.foo_id
AND foo_bar.bar_id = 1) ...
Crazy thing is, that single optimization reduced the query time
significantly, from 2.5-3 seconds down to 40-60ms.
Does anyone have any kind of explanation for this? Are the inner
workings of the IN clause taking the plan for the subselect into account
when running, and doing something clever with it? Any insight on the
internal mechanisms of IN or subselects in Postgres would be greatly
appreciated if anyone knows more.
Also, are there any better ways you can think of doing such an IN query,
using non-subselect means that might be more efficient?
Thanks in advance, any advice/help understanding this better is greatly
appreciated.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about subselect/IN performance
2010-11-30 17:43 Question about subselect/IN performance T.H. <calinet6@gmail.com>
@ 2010-11-30 22:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-30 23:23 ` Re: Question about subselect/IN performance T.H. <calinet6@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2010-11-30 22:54 UTC (permalink / raw)
To: T.H. <calinet6@gmail.com>; pgsql-performance
"T.H." <calinet6@gmail.com> wrote:
> Also, are there any better ways you can think of doing such an IN
> query, using non-subselect means that might be more efficient?
Have you tried the EXISTS predicate?
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about subselect/IN performance
2010-11-30 17:43 Question about subselect/IN performance T.H. <calinet6@gmail.com>
2010-11-30 22:54 ` Re: Question about subselect/IN performance Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2010-11-30 23:23 ` T.H. <calinet6@gmail.com>
2010-12-01 01:32 ` Re: Question about subselect/IN performance bricklen <bricklen@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: T.H. @ 2010-11-30 23:23 UTC (permalink / raw)
To: pgsql-performance
On 11/30/10 5:54 PM, Kevin Grittner wrote:
> "T.H."<calinet6@gmail.com> wrote:
>
>> Also, are there any better ways you can think of doing such an IN
>> query, using non-subselect means that might be more efficient?
>
> Have you tried the EXISTS predicate?
>
> -Kevin
>
Just looking into it now, thanks for the suggestion. Is there a reason
that EXISTS is generally faster than IN for this sort of query?
-Tristan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Question about subselect/IN performance
2010-11-30 17:43 Question about subselect/IN performance T.H. <calinet6@gmail.com>
2010-11-30 22:54 ` Re: Question about subselect/IN performance Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-30 23:23 ` Re: Question about subselect/IN performance T.H. <calinet6@gmail.com>
@ 2010-12-01 01:32 ` bricklen <bricklen@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: bricklen @ 2010-12-01 01:32 UTC (permalink / raw)
To: T.H. <calinet6@gmail.com>; +Cc: pgsql-performance
On Tue, Nov 30, 2010 at 3:23 PM, T.H. <calinet6@gmail.com> wrote:
> Just looking into it now, thanks for the suggestion. Is there a reason that
> EXISTS is generally faster than IN for this sort of query?
>
> -Tristan
Exists will return immediately upon finding a match -- assuming there is one.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-12-01 01:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-30 17:43 Question about subselect/IN performance T.H. <calinet6@gmail.com>
2010-11-30 22:54 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-11-30 23:23 ` T.H. <calinet6@gmail.com>
2010-12-01 01:32 ` bricklen <bricklen@gmail.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