Here is the explain analyze of the query:
explain analyze select count(*) from actvars, prodlevel where
actvars.product_level=prodlevel.code_level and
prodlevel.division_level='OY3S5LAPALL6';
Aggregate (cost=3123459.62..3123459.62 rows=1 width=32) (actual
time=1547173.60..1547173.60 rows=1 loops=1)
-> Merge Join (cost=3021564.79..3119827.17 rows=1452981 width=32) (actual
time=1400269.29..1545793.13 rows=1918466 loops=1)
Merge Cond: ("outer".product_level = "inner".code_level)
-> Sort (cost=3020875.00..3060938.81 rows=16025523 width=16) (actual
time=1400117.06..1518059.84 rows=16020985 loops=1)
Sort Key: actvars.product_level
-> Seq Scan on actvars (cost=0.00..365711.23 rows=16025523
width=16) (actual time=29.14..51259.82 rows=16025523 loops=1)
-> Sort (cost=689.79..694.48 rows=1877 width=16) (actual
time=92.90..1217.15 rows=1917991 loops=1)
Sort Key: prodlevel.code_level
-> Seq Scan on prodlevel (cost=0.00..587.75 rows=1877 width=16)
(actual time=16.48..82.72 rows=1802 loops=1)
Filter: (division_level = 'OY3S5LAPALL6'::bpchar)
Total runtime: 1547359.08 msec
I have tried diabeling the seqscan:
set enable_seqscan=false;
explain select count(*) from actvars, prodlevel where
actvars.product_level=prodlevel.code_level and
prodlevel.division_level='OY3S5LAPALL6';
Aggregate (cost=6587448.25..6587448.25 rows=1 width=32)
-> Nested Loop (cost=0.00..6583815.80 rows=1452981 width=32)
-> Index Scan using division_level_prodlevel_index on prodlevel
(cost=0.00..999.13 rows=1877 width=16)
Index Cond: (division_level = 'OY3S5LAPALL6'::bpchar)
-> Index Scan using product_level_actvars_index on actvars
(cost=0.00..3492.95 rows=1161 width=16)
Index Cond: (actvars.product_level = "outer".code_level)
This method forces the indices to work but it looks like it takes a long to
finish executing, I had to cancel the query after 10 min. Using vmstat i found
that there were alot of swap outs and swap ins, affecting the overall performance.
How can i speed this
up?
---------------------------------------------
This message was sent using Endymion MailMan.
http://www.endymion.com/products/mailman/
