Обсуждение: Limit changes query plan
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi all,
I'm using 8.2.6 and I'm observing a trange behaviour using
offset and limits.
This are the two queries that are puzzling me:
explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM
t_OA_2_00_cardc JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550
ORDERBY nctr,nctn,ncts,rvel offset 0 ; QUERY PLAN
- ----------------------------------------------------------------------------------------------------------------
Limit (cost=175044.75..175071.04 rows=10518 width=90) -> Sort (cost=175044.75..175071.04 rows=10518 width=90)
SortKey: c.nctr, c.nctn, c.ncts, c.rvel -> Hash Join (cost=25830.72..174342.12 rows=10518 width=90)
HashCond: (c.id = dt.card_id) -> Bitmap Heap Scan on t_oa_2_00_card c (cost=942.36..148457.19 rows=101872
width=90) Recheck Cond: (ecp = 18) -> Bitmap Index Scan on i7_t_oa_2_00_card
(cost=0.00..916.89rows=101872 width=0) Index Cond: (ecp = 18) -> Hash
(cost=22743.45..22743.45rows=171593 width=8) -> Bitmap Heap Scan on t_oa_2_00_dt dt
(cost=2877.26..22743.45rows=171593 width=8) Recheck Cond: (_from <= 1550)
Filter: (_to >= 1500) -> Bitmap Index Scan on i_oa_2_00_dt_from (cost=0.00..2834.36
rows=182546width=0) Index Cond: (_from <= 1550)
explain SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM
t_OA_2_00_cardc JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550
ORDERBY nctr,nctn,ncts,rvel offset 0 limit 5; QUERY PLAN
- --------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2125.12 rows=5 width=90) -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) ->
IndexScan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90)
Filter:(ecp = 18) -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8)
Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from <= 1550))
using the limit I have an execution time of minutes vs a some seconds.
What am I missing here ?
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFHov3I7UpzwH2SGd4RApR+AJ0dG/+0MoB3PMD1kRgQt0BisHwQBACgzVwC
BN/SBWrvVxVE9eBLK0C1Pnw=
=9Ucp
-----END PGP SIGNATURE-----
On Fri, Feb 01, 2008 at 12:08:56PM +0100, Gaetano Mendola wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Hi all, > I'm using 8.2.6 and I'm observing a trange behaviour using > offset and limits. Please post EXPLAIN ANALYZE output so we can see what's actually taking the time. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Those who make peaceful revolution impossible will make violent revolution inevitable. > -- John F Kennedy
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Martijn van Oosterhout wrote: > On Fri, Feb 01, 2008 at 12:08:56PM +0100, Gaetano Mendola wrote: >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> Hi all, >> I'm using 8.2.6 and I'm observing a trange behaviour using >> offset and limits. > > Please post EXPLAIN ANALYZE output so we can see what's actually taking > the time. The analyze is still running (I launched it 30 mins ago), I'll post it as soon I have it. Disabling the nested_loop ( set enable_nestloop = false ) the query with the limit has now the same execution time without the limit. I don't get why a limit is going to change the query plan and most of all decreasing the performances. Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHowXA7UpzwH2SGd4RAomqAJ409579Jk7d5FYWf92PjOYDRxWNIQCggg1w 1WJcVmn2g1MASBGh9OtCQ0Q= =h2Z6 -----END PGP SIGNATURE-----
"Gaetano Mendola" <mendola@bigfoot.com> writes: > I don't get why a limit is going to change the query plan and most of all decreasing > the performances. Until we see the explain analyze it won't be clear what exactly is going on. But in theory a LIMIT can definitely change the plan because the planner knows it won't need to generate all the rows to satisfy the LIMIT. In the plans you gave note that the plan for the unlimited query has a Sort so it has to produce all the records every time. The second query produces the records in order so if the LIMIT is satisfied quickly then it can save a lot of work. It's evidently guessing wrong about the limit being satisfied early. The non-indexed restrictions might be pruning out a lot more records than the planner expects. Or possibly the table is just full of dead records. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support!
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Gregory Stark wrote:
> "Gaetano Mendola" <mendola@bigfoot.com> writes:
>
>> I don't get why a limit is going to change the query plan and most of all decreasing
>> the performances.
>
> Until we see the explain analyze it won't be clear what exactly is going on.
> But in theory a LIMIT can definitely change the plan because the planner knows
> it won't need to generate all the rows to satisfy the LIMIT.
>
> In the plans you gave note that the plan for the unlimited query has a Sort so
> it has to produce all the records every time. The second query produces the
> records in order so if the LIMIT is satisfied quickly then it can save a lot
> of work.
>
> It's evidently guessing wrong about the limit being satisfied early. The
> non-indexed restrictions might be pruning out a lot more records than the
> planner expects. Or possibly the table is just full of dead records.
>
Here the analyze result:
explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM
t_OA_2_00_cardc JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY
nctr,nctn,ncts,rveloffset 0 limit 5;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2125.12 rows=5 width=90) (actual time=3399923.424..3399960.174 rows=5 loops=1) -> Nested Loop
(cost=0.00..4470402.02rows=10518 width=90) (actual time=3399923.420..3399960.156 rows=5 loops=1) -> Index Scan
usingi_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90) (actual
time=3399892.632..3399896.773rows=50 loops=1) Filter: (ecp = 18) -> Index Scan using
i_oa_2_00_dt_foron t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from <= 1550))
Total runtime: 3399960.277 ms
explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM
t_OA_2_00_cardc JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY
nctr,nctn,ncts,rveloffset 0 ; QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=175044.75..175071.04 rows=10518 width=90) (actual time=2425.138..2435.633 rows=3298 loops=1) -> Sort
(cost=175044.75..175071.04rows=10518 width=90) (actual time=2425.134..2428.812 rows=3298 loops=1) Sort Key:
c.nctr,c.nctn, c.ncts, c.rvel -> Hash Join (cost=25830.72..174342.12 rows=10518 width=90) (actual
time=797.540..2382.900rows=3298 loops=1) Hash Cond: (c.id = dt.card_id) -> Bitmap Heap Scan on
t_oa_2_00_cardc (cost=942.36..148457.19 rows=101872 width=90) (actual time=70.212..1507.429 rows=97883 loops=1)
Recheck Cond: (ecp = 18) -> Bitmap Index Scan on i7_t_oa_2_00_card (cost=0.00..916.89
rows=101872width=0) (actual time=53.340..53.340 rows=97883 loops=1) Index Cond: (ecp = 18)
-> Hash (cost=22743.45..22743.45 rows=171593 width=8) (actual time=726.597..726.597 rows=89277 loops=1)
-> Bitmap Heap Scan on t_oa_2_00_dt dt (cost=2877.26..22743.45 rows=171593 width=8) (actual
time=86.181..593.275rows=89277 loops=1) Recheck Cond: (_from <= 1550)
Filter:(_to >= 1500) -> Bitmap Index Scan on i_oa_2_00_dt_from (cost=0.00..2834.36
rows=182546width=0) (actual time=80.863..80.863 rows=201177 loops=1) Index Cond: (_from
<=1550)
Total runtime: 2440.396 ms
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFHoytQ7UpzwH2SGd4RAujPAKDkM53sirwNFa7jH/Q3R2y1/QAcKQCgn9VH
pUSwTkR3c963BoCbNwG+W6Y=
=s7Vr
-----END PGP SIGNATURE-----
Gaetano Mendola <mendola@bigfoot.com> writes:
> Gregory Stark wrote:
>> It's evidently guessing wrong about the limit being satisfied early. The
>> non-indexed restrictions might be pruning out a lot more records than the
>> planner expects. Or possibly the table is just full of dead records.
> Here the analyze result:
> explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM
t_OA_2_00_cardc JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY
nctr,nctn,ncts,rveloffset 0 limit 5;
> QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..2125.12 rows=5 width=90) (actual time=3399923.424..3399960.174 rows=5 loops=1)
> -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) (actual time=3399923.420..3399960.156 rows=5 loops=1)
> -> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90)
(actualtime=3399892.632..3399896.773 rows=50 loops=1)
> Filter: (ecp = 18)
> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual
time=1.264..1.264rows=0 loops=50)
> Index Cond: (dt.card_id = c.id)
> Filter: ((_to >= 1500) AND (_from <= 1550))
> Total runtime: 3399960.277 ms
It's guessing that there are 101872 rows altogether that have ecp = 18.
Is that about right? If not, raising the statistics target for the
table might fix the problem. If it is about right, then you may be
stuck --- the problem then could be that the rows with ecp=18 aren't
uniformly scattered in the i_oa_2_00_card_keys ordering, but are
clustered near the end.
Greg's comment about dead rows might be correct too --- the actual
runtime for the indexscan seems kinda high even if it is scanning most
of the table. Also, if this query is important enough, clustering
by that index would improve matters, at the cost of possibly slowing
down other queries that use other indexes.
regards, tom lane
<br /><p><font size="2">> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1width=8) (actual time=1.264..1.264 rows=0 loops=50)<br /> > Index Cond: (dt.card_id = c.id)<br />> Filter: ((_to >= 1500) AND (_from <= 1550))<br /> > Total runtime: 3399960.277 ms<br /><br/> Also, are 1500 and 1550 user-supplied parameters or are they part of a small set of possible values? You could considerhaving a partial index on "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to matchexactly as long as they include all the records the query needs.<br /><br /> Another possibility is using somethinglike "cube" from contrib to build a GIST index on <_to,_from>. I think you would need to load gist_btree aswell for the first column on card_id. It doesn't help every use case though, you would have to experiment.<br /><br />But before experimenting with either of those things, what does "VACUUM VERBOSE t_oa_2_00_dt" say?</font>
"Greg Stark" <greg.stark@enterprisedb.com> writes:
>> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264
rows=0loops=50)
>> Index Cond: (dt.card_id = c.id)
>> Filter: ((_to >= 1500) AND (_from <= 1550))
>> Total runtime: 3399960.277 ms
> Also, are 1500 and 1550 user-supplied parameters or are they part of a small set of possible values? You could
considerhaving a partial index on "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to match
exactlyas long as they include all the records the query needs.
That side of the join isn't where the problem is, though.
If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel
would probably fix the performance issue very nicely.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: > "Greg Stark" <greg.stark@enterprisedb.com> writes: >>> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264rows=0 loops=50) >>> Index Cond: (dt.card_id = c.id) >>> Filter: ((_to >= 1500) AND (_from <= 1550)) >>> Total runtime: 3399960.277 ms > >> Also, are 1500 and 1550 user-supplied parameters or are they part of a small set of possible values? You could considerhaving a partial index on "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to match exactlyas long as they include all the records the query needs. > > That side of the join isn't where the problem is, though. > > If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel > would probably fix the performance issue very nicely. > As always you are right, creating the index "ivan" btree (ecp, nctr, nctn, ncts, rvel) that query with the limit responds now in the blink of an eye: > explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_cardc JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDERBY nctr,nctn,ncts,rvel offset 0 limit 5; QUERYPLAN - -----------------------------------------------------------------------------------------------------------------------------------------------Limit (cost=0.00..370.03 rows=5 width=90) (actual time=0.102..0.608 rows=5 loops=1) -> Nested Loop (cost=0.00..778392.80 rows=10518width=90) (actual time=0.099..0.594 rows=5 loops=1) -> Index Scan using ivan on t_oa_2_00_card c (cost=0.00..235770.34rows=101872 width=90) (actual time=0.024..0.134 rows=50 loops=1) Index Cond: (ecp = 18) -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=0.006..0.006rows=0 loops=50) Index Cond: (dt.card_id = c.id) Filter: ((_to >= 1500) AND (_from<= 1550))Total runtime: 0.700 ms (8 rows) Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHo1SB7UpzwH2SGd4RAhTeAJ0WL49jjUgCWSrNopV/8L+rbOLaEgCfTDlh crAHZYxxTYz6VqTDggqW7x0= =dKey -----END PGP SIGNATURE-----