Tom Lane wrote:
"Greg Stark" <greg.stark@enterprisedb.com> writes:
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.
"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.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.Index Cond: (dt.card_id = c.id)
Filter: ((_to >= 1500) AND (_from <= 1550))
Total runtime: 3399960.277 ms
If you're willing to invent new indexes, one on ecp,nctr,nctn,ncts,rvel
would probably fix the performance issue very nicely.
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
