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