Re: Limit changes query plan

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