Re: Limit changes query plan

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Tom Lane wrote:
> "Greg Stark" <greg(dot)stark(at)enterprisedb(dot)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.264 rows=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 consider having a partial index on "card_id WHERE _to >= 1500 AND _from <= 1550". The numbers don't even have to match exactly as 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_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..370.03 rows=5 width=90) (actual time=0.102..0.608 rows=5 loops=1)
-> Nested Loop (cost=0.00..778392.80 rows=10518 width=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.34 rows=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.006 rows=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-----