From: | Gaetano Mendola <mendola(at)bigfoot(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Limit changes query plan |
Date: | 2008-02-01 11:08:56 |
Message-ID: | 47A2FDC8.1010403@bigfoot.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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_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)
-> Sort (cost=175044.75..175071.04 rows=10518 width=90)
Sort Key: c.nctr, c.nctn, c.ncts, c.rvel
-> Hash Join (cost=25830.72..174342.12 rows=10518 width=90)
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)
Recheck Cond: (ecp = 18)
-> Bitmap Index Scan on i7_t_oa_2_00_card (cost=0.00..916.89 rows=101872 width=0)
Index Cond: (ecp = 18)
-> Hash (cost=22743.45..22743.45 rows=171593 width=8)
-> Bitmap Heap Scan on t_oa_2_00_dt dt (cost=2877.26..22743.45 rows=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=182546 width=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_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)
-> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90)
-> 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)
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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Martijn van Oosterhout | 2008-02-01 11:16:09 | Re: Limit changes query plan |
Previous Message | Tom Lane | 2008-02-01 08:13:59 | Re: [mike.aubury@aubit.com: PGconn ?] |