Re: Limit changes query plan - Mailing list pgsql-hackers

-----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-----