pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedHelp me understand why my subselect is an order of magnitude faster than my nested joins
2+ messages / 2 participants
[nested] [flat]
* Help me understand why my subselect is an order of magnitude faster than my nested joins
@ 2014-03-03 18:24 Eli Naeher <enaeher@gmail.com>
2014-03-18 21:20 ` Re: Help me understand why my subselect is an order of magnitude faster than my nested joins Vincent <vinny@xs4all.nl>
0 siblings, 1 reply; 2+ messages in thread
From: Eli Naeher @ 2014-03-03 18:24 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have two versions of essentially the same query; one using nested joins,
the other using subselects. The version using the subselect is roughly an
order of magnitude faster (~70ms on my box and data vs ~900ms for the
nested joins). Of course the obvious answer here is just to use the faster
version, but I'd like to understand why the other version is so slow. These
queries are automatically generated by our code and I'd like to feel more
informed when deciding what style of query it should be generating (and to
know whether there is a way to write the nested-join queries that will more
closely approach the performance of the subselect).
(The table aliasing is an artifact of the code that is generating this
query--I assume there is no big performance impact there, but perhaps that
assumption is mistaken.)
The join version:
(SELECT DISTINCT resource_type_1.*
FROM resource_type AS resource_type_1
LEFT JOIN group_authorization AS group_authorization_2
INNER JOIN group_member AS group_member_4
ON ( ( group_authorization_2.person_oid =
group_member_4.person_oid )
AND ( group_authorization_2.group_oid =
group_member_4.group_oid ) )
INNER JOIN wco_group AS group_5
ON ( group_authorization_2.group_oid =
group_5.obj_oid )
ON ( resource_type_1.obj_oid =
group_authorization_2.rtype_oid )
WHERE ( ( ( ( ( group_5.end_date IS NULL )
OR ( group_5.end_date >= '2014-03-03T18:08:23.543001Z' ) )
AND ( ( group_member_4.expire IS NULL )
OR ( group_member_4.expire >=
'2014-03-03T18:08:23.543001Z'
) )
AND ( ( group_authorization_2.expire IS NULL )
OR ( group_authorization_2.expire >=
'2014-03-03T18:08:23.543001Z'
)
)
)
AND ( group_authorization_2.person_oid = 1 ) )
OR ( resource_type_1.authorized = false ) ))
(explain (analyze, buffers) output is at http://explain.depesz.com/s/wPZL)
The subselect version:
(SELECT DISTINCT resource_type_1.*
FROM resource_type AS resource_type_1
WHERE ( ( resource_type_1.authorized = false )
OR ( resource_type_1.obj_oid IN (SELECT rtype_oid
FROM group_authorization
INNER JOIN group_member
ON ( (
group_member.group_oid
=
group_authorization.group_oid )
AND ( group_member.person_oid =
group_authorization.person_oid ) )
INNER JOIN wco_group
ON ( group_member.group_oid = wco_group.obj_oid )
WHERE ( ( group_member.person_oid = 1 )
AND ( ( group_authorization.expire >
'2014-03-03T18:11:20.553844Z' )
OR ( group_authorization.expire IS NULL ) )
AND ( ( group_member.expire > '2014-03-03T18:11:20.553844Z'
)
OR ( group_member.expire IS NULL ) )
AND ( ( wco_group.end_date > '2014-03-03T18:11:20.553844Z'
)
OR ( wco_group.end_date IS NULL ) ) )) ) ))
(explain (analyze, buffers) output is at http://explain.depesz.com/s/70dd)
This is using Postgres 9.3.3. The table wco_group has ~5000 rows,
group_member has ~15000 rows, and group_authorization is the big one with
~385000 rows.
I noticed that the nested join version was doing a lot of seq scans and not
using the indexes. I tried setting enable_seqscan to off to force index
use, and it was a bit slower that way, so the query optimizer is definitely
doing the right thing.
Any thoughts would be much appreciated.
Thank you,
-Eli
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Help me understand why my subselect is an order of magnitude faster than my nested joins
2014-03-03 18:24 Help me understand why my subselect is an order of magnitude faster than my nested joins Eli Naeher <enaeher@gmail.com>
@ 2014-03-18 21:20 ` Vincent <vinny@xs4all.nl>
0 siblings, 0 replies; 2+ messages in thread
From: Vincent @ 2014-03-18 21:20 UTC (permalink / raw)
To: pgsql-performance
On 03-03-14 19:24, Eli Naeher wrote:
> Hello,
>
> I have two versions of essentially the same query; one using nested
> joins, the other using subselects. The version using the subselect is
> roughly an order of magnitude faster (~70ms on my box and data vs
> ~900ms for the nested joins). Of course the obvious answer here is
> just to use the faster version, but I'd like to understand why the
> other version is so slow. These queries are automatically generated by
> our code and I'd like to feel more informed when deciding what style
> of query it should be generating (and to know whether there is a way
> to write the nested-join queries that will more closely approach the
> performance of the subselect).
>
> (The table aliasing is an artifact of the code that is generating this
> query--I assume there is no big performance impact there, but perhaps
> that assumption is mistaken.)
>
> The join version:
>
> (SELECT DISTINCT resource_type_1.*
> FROM resource_type AS resource_type_1
> LEFT JOIN group_authorization AS group_authorization_2
> INNER JOIN group_member AS group_member_4
> ON ( ( group_authorization_2.person_oid =
> group_member_4.person_oid )
> AND ( group_authorization_2.group_oid =
> group_member_4.group_oid ) )
> INNER JOIN wco_group AS group_5
> ON ( group_authorization_2.group_oid =
> group_5.obj_oid )
> ON ( resource_type_1.obj_oid =
> group_authorization_2.rtype_oid )
> WHERE ( ( ( ( ( group_5.end_date IS NULL )
> OR ( group_5.end_date >=
> '2014-03-03T18:08:23.543001Z' ) )
> AND ( ( group_member_4.expire IS NULL )
> OR ( group_member_4.expire >=
> '2014-03-03T18:08:23.543001Z'
> ) )
> AND ( ( group_authorization_2.expire IS NULL )
> OR ( group_authorization_2.expire >=
> '2014-03-03T18:08:23.543001Z'
> )
> )
> )
> AND ( group_authorization_2.person_oid = 1 ) )
> OR ( resource_type_1.authorized = false ) ))
>
> (explain (analyze, buffers) output is at http://explain.depesz.com/s/wPZL)
>
> The subselect version:
>
> (SELECT DISTINCT resource_type_1.*
> FROM resource_type AS resource_type_1
> WHERE ( ( resource_type_1.authorized = false )
> OR ( resource_type_1.obj_oid IN (SELECT rtype_oid
> FROM group_authorization
> INNER JOIN group_member
> ON ( (
> group_member.group_oid
> =
> group_authorization.group_oid )
> AND ( group_member.person_oid =
> group_authorization.person_oid ) )
> INNER JOIN wco_group
> ON ( group_member.group_oid = wco_group.obj_oid )
> WHERE ( ( group_member.person_oid = 1 )
> AND ( ( group_authorization.expire >
> '2014-03-03T18:11:20.553844Z' )
> OR ( group_authorization.expire IS NULL ) )
> AND ( ( group_member.expire >
> '2014-03-03T18:11:20.553844Z' )
> OR ( group_member.expire IS NULL ) )
> AND ( ( wco_group.end_date >
> '2014-03-03T18:11:20.553844Z' )
> OR ( wco_group.end_date IS NULL ) ) )) ) ))
>
> (explain (analyze, buffers) output is at http://explain.depesz.com/s/70dd)
>
> This is using Postgres 9.3.3. The table wco_group has ~5000 rows,
> group_member has ~15000 rows, and group_authorization is the big one
> with ~385000 rows.
>
> I noticed that the nested join version was doing a lot of seq scans
> and not using the indexes. I tried setting enable_seqscan to off to
> force index use, and it was a bit slower that way, so the query
> optimizer is definitely doing the right thing.
>
> Any thoughts would be much appreciated.
>
> Thank you,
> -Eli
The explains show that the join version builds up an ever larger set of
rows before finally filtering,
while the subselect manages to reduce the number of rows to 2500 and
avoids the large set.
This may be as simple as the order in which you join, inner join's
should preferably eliminate as many rows
as possible as quickly as possible.
Also, DISTINCT on * does not help, why are you getting duplicates and
why can't you filter them out before doing the final select?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2014-03-18 21:20 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-03-03 18:24 Help me understand why my subselect is an order of magnitude faster than my nested joins Eli Naeher <enaeher@gmail.com>
2014-03-18 21:20 ` Vincent <vinny@xs4all.nl>
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