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_card c 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,rvel offset 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) (actual time=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.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_card c 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,rvel offset 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.04 rows=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.900 rows=3298 loops=1)
Hash Cond: (c.id = dt.card_id)
-> Bitmap Heap Scan on t_oa_2_00_card c (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=101872 width=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.275 rows=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=182546 width=0) (actual time=80.863..80.863 rows=201177 loops=1)
Index Cond: (_from <= 1550)
Total runtime: 2440.396 ms



Regards
Gaetano Mendola

Search Discussions

Discussion Posts

Previous

Follow ups

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 5 of 9 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 1, '08 at 11:08a
activeFeb 1, '08 at 5:18p
posts9
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase