Query Plan - Mailing list pgsql-sql

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.32rows=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
---