a plan for modified query is
ohs=# explain analyze SELECT object_id,
inserted,
'ASSIGN_RSLT',
order_id,
2,
seqnum,
rejected_flat_file_id,
true
FROM (
SELECT q.object_id,
fe.inserted,
q.order_id,
q.seqnum,
q.rejected_flat_file_id,
q.rejected_result
FROM queue q
JOIN
outgoing.cps_forms f
ON f.id = q.object_id AND q.object_type = 'cp'
JOIN
flat_file_ex fe
ON fe.id = q.rejected_flat_file_id
offset 0) x
WHERE rejected_result = 'ACTV';
QUERY PLAN
────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────
Subquery Scan on x (cost=11.68..192.72 rows=1 width=24) (actual
time=1.748..12.398 rows=139 loops=1)
Filter: (x.rejected_result = 'ACTV'::bpchar)
Rows Removed by Filter: 17
-> Limit (cost=11.68..192.65 rows=6 width=29) (actual
time=1.739..11.655 rows=156 loops=1)
-> Nested Loop (cost=11.68..192.65 rows=6 width=29) (actual
time=1.732..11.036 rows=156 loops=1)
-> Hash Join (cost=11.68..138.77 rows=15 width=21)
(actual time=1.459..6.987 rows=186 loops=1)
Hash Cond: (q.object_id = f.id)
-> Seq Scan on queue q (cost=0.00..126.24
rows=186 width=21) (actual time=0.032..4.658 rows=186 loops=1)
Filter: (object_type = 'cp'::bpchar)
Rows Removed by Filter: 4313
-> Hash (cost=9.08..9.08 rows=208 width=4)
(actual time=1.402..1.402 rows=208 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 5kB
-> Seq Scan on cps_forms f
(cost=0.00..9.08 rows=208 width=4) (actual time=0.008..0.576 rows=208
loops=1)
-> Index Scan using flat_file_ex_pkey on flat_file_ex
fe (cost=0.00..3.58 rows=1 width=12) (actual time=0.008..0.010 rows=1
loops=186)
Index Cond: (id = q.rejected_flat_file_id)
Total runtime: 12.846 ms
(16 rows)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
