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

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 9 of 9 | next ›
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 1, '08 at 11:08a
activeFeb 1, '08 at 5:18p
posts9
users5
websitepostgresql.org...
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase