Limit changes query plan - Mailing list pgsql-hackers

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