Query Plan

Hi,

I have a complicated view joining 3 tables.
Here are the run times:

LOG: duration: 3380.672 ms statement: explain analyze SELECT * from
vtradeblock where FirmClearingid = 'FIRMA' and status = 1;

LOG: duration: 3784.152 ms statement: explain analyze SELECT * from
vtradeblock where date_trunc('day',tradedate) = '20070703';

LOG: duration: 19631.958 ms statement: EXPLAIN ANALYZE SELECT * from
vtradeblock where FirmClearingid = 'ALLIANCE' and status = 1 and
date_trunc('day', tradedate) = '20070703';

When I run the query with combination of FirmClearingID & status the run
times are approx 3700ms.
But when I add tradedate ie date_trunc('day', tradedate) = '20070703' the
run time becomes a horrendous 19631.958 ms.

I am displaying the query plan from Explain Analyze:

Query 1 (without date_trunc)

Hash Left Join (cost=103783.42..104890.34 rows=154 width=992) (actual
time=3744.962..3781.749 rows=149 loops=1)
Hash Cond: (t.blockid = a.blockid)
-> Nested Loop (cost=17359.18..18450.32 rows=154 width=912) (actual
time=840.575..842.620 rows=149 loops=1)
-> HashAggregate (cost=17359.18..17366.50 rows=154 width=356)
(actual time=840.489..841.018 rows=149 loops=1)
-> Seq Scan on tradetbl (cost=0.00..17309.26 rows=1536
width=356) (actual time=828.272..838.045 rows=406 loops=1)
Filter: (date_trunc('day'::text,
(date_trunc('day'::text, exectime) +
'12:00:00'::interval)) = '2007-07-03
00:00:00'::timestamp without time zone)
-> Index Scan using tradeblocktbl_pkey on tradeblocktbl tr
(cost=0.00..7.02 rows=1 width=618) (actual time=0.006..0.007
rows=1 loops=149)
Index Cond: (t.blockid = tr.recid)
-> Hash (cost=86423.74..86423.74 rows=200 width=84) (actual
time=2873.368..2873.368 rows=118694 loops=1)
-> Subquery Scan a (cost=0.00..86423.74 rows=200 width=84)
(actual time=0.363..2643.006 rows=118694 loops=1)
-> GroupAggregate (cost=0.00..86421.74 rows=200 width=32)
(actual time=0.359..2461.812 rows=118694 loops=1)
-> GroupAggregate (cost=0.00..85417.24 rows=40000
width=12) (actual time=0.279..1825.833 rows=118696
loops=1)
-> Index Scan using k_alloctbl_blockid_status
on alloctbl (cost=0.00..73516.91 rows=588590
width=12) (actual time=0.069..871.672
rows=588590 loops=1)
Total runtime: 3782.349 ms
(14 rows)

Query Plan 2 (with date trunc)

Nested Loop Left Join (cost=4269.91..90708.79 rows=1 width=992) (actual
time=2455.184..19629.407 rows=8 loops=1)
Join Filter: (t.blockid = a.blockid)
Filter: (CASE WHEN ((a.qty_ready = 0) AND (a.qty_submitted = 0)) THEN 0
WHEN ((a.qty_ready = 0) AND (a.qty_submitted = t.netshares)) THEN 1
WHEN (a.qty_ready > 0) THEN 2 WHEN ((a.qty_submitted > 0) AND
(a.qty_ready = 0) AND (a.qty_submitted < t.netshares)) THEN 3 ELSE 0
END = 1)
-> Nested Loop (cost=4269.91..4278.01 rows=1 width=912) (actual
time=28.149..28.674 rows=8 loops=1)
-> HashAggregate (cost=4269.91..4269.96 rows=1 width=356)
(actual time=28.073..28.151 rows=8 loops=1)
-> Bitmap Heap Scan on tradetbl (cost=31.56..4269.65
rows=8 width=356) (actual time=27.193..27.878 rows=20
loops=1)
Recheck Cond: ((firmclearingid)::text = 'FIRMA'::text)
Filter: (date_trunc('day'::text,
(date_trunc('day'::text, exectime) +
'12:00:00'::interval)) = '2007-07-03
00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on k_tradetbl_firmclearingid
(cost=0.00..31.56 rows=1536 width=0) (actual
time=1.201..1.201 rows=2643 loops=1)
Index Cond: ((firmclearingid)::text =
'FIRMA'::text)
-> Index Scan using tradeblocktbl_pkey on tradeblocktbl tr
(cost=0.00..8.03 rows=1 width=618) (actual time=0.052..0.055
rows=1 loops=8)
Index Cond: (t.blockid = tr.recid)
-> GroupAggregate (cost=0.00..86421.74 rows=200 width=32) (actual
time=0.300..2363.811 rows=118694 loops=8)
-> GroupAggregate (cost=0.00..85417.24 rows=40000 width=12)
(actual time=0.244..1766.637 rows=118696 loops=8)
-> Index Scan using k_alloctbl_blockid_status on alloctbl
(cost=0.00..73516.91 rows=588590 width=12) (actual
time=0.060..854.834 rows=588590 loops=8)
Total runtime: 19629.777 ms
(16 rows)

The Trade table is being indexed by symbol, side, firmclearingid.
Even when i add an index to the date, ie date_trunc, it is not helping.

Any suggestions?

Thanks,
Radhika

--
It is all a matter of perspective. You choose your view by choosing where
to stand.
Larry Wall
---