-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Tom Lane wrote:
> "Greg Stark" <[EMAIL PROTECTED]> 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-----
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
