pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: rantunes@student.dei.uc.pt
To: pgsql-performance@postgresql.org
Subject: Indices arent being used
Date: Thu, 25 Sep 2003 22:28:40 GMT
Message-ID: <200309252228.h8PMSeXA009897@student.dei.uc.pt> (raw)

Hi guys 

Im running a Datawarehouse benchmark (APB-1) on PostgreSql. The objective is to
choose which of the to main db (PostgreSQL, MySQL) is fastest. I've run into a
small problem which I hope could be resolved here.

I'm trying to speed up this query:

select count(*) from actvars, prodlevel where
actvars.product_level=prodlevel.code_level and
prodlevel.division_level='OY3S5LAPALL6';

ACTVARS is a fact table of aproximatly 16 million rows, PRODLEVEL has 20700
rows. Both have btree indices. 

I executed the query and it took me almost half an hour to execute! Running the
same query on MySQL the result came 6 seconds after. As you can see there is a
large differences between execution times.

After running an explain:

Aggregate  (cost=3123459.62..3123459.62 rows=1 width=32)
   ->  Merge Join  (cost=3021564.79..3119827.17 rows=1452981 width=32)
         Merge Cond: ("outer".product_level = "inner".code_level)
         ->  Sort  (cost=3020875.00..3060938.81 rows=16025523 width=16)
               Sort Key: actvars.product_level
               ->  Seq Scan on actvars  (cost=0.00..365711.23 rows=16025523
width=16)
         ->  Sort  (cost=689.79..694.48 rows=1877 width=16)
               Sort Key: prodlevel.code_level
               ->  Seq Scan on prodlevel  (cost=0.00..587.75 rows=1877 width=16)
                     Filter: (division_level = 'OY3S5LAPALL6'::bpchar)

I found that the indices werent being used. 

The database has been vacuumed and analyze has been executed.

I tried disabling the seqscan, so as to force index usage. The planner uses
index scans but the query stil takes a very long time to execute.

Any suggestions on resolving this would would be appreciated.

P.S: Im running PostgrSQL
7.3.2

---------------------------------------------
This message was sent using Endymion MailMan.
http://www.endymion.com/products/mailman/





view thread (7+ messages)  latest in thread

Message-ID: <200309252228.h8PMSeXA009897@student.dei.uc.pt>
Permalink:  ../200309252228.h8PMSeXA009897@student.dei.uc.pt/
Also on:    postgresql.org/message-id/200309252228.h8PMSeXA009897@student.dei.uc.pt

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rantunes@student.dei.uc.pt
  Subject: Re: Indices arent being used
  In-Reply-To: <200309252228.h8PMSeXA009897@student.dei.uc.pt>
  References: <200309252228.h8PMSeXA009897@student.dei.uc.pt>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox