Skip site navigation (1) Skip section navigation (2)

Re: Indices arent being used

From: rantunes(at)student(dot)dei(dot)uc(dot)pt
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Indices arent being used
Date: 2003-09-27 14:48:58
Message-ID: 200309271448.h8REmwXA008210@student.dei.uc.pt (view raw or flat)
Thread:
Lists: pgsql-performance
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/



Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2003-09-27 17:14:02
Subject: Re: Indices arent being used
Previous:From: Bruce MomjianDate: 2003-09-26 17:47:22
Subject: Re: [PERFORM] PostgreSQL vs. MySQL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group