pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: rantunes@student.dei.uc.pt
Cc: pgsql-performance@postgresql.org
Subject: Re: Indices arent being used
Date: Fri, 26 Sep 2003 12:55:15 +0530
Message-ID: <3F73E9DB.80104@persistent.co.in> (raw)
In-Reply-To: <200309252228.h8PMSeXA009897@student.dei.uc.pt>
References: <200309252228.h8PMSeXA009897@student.dei.uc.pt>

rantunes@student.dei.uc.pt wrote:
> 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)

Damn.. Seq. scan for actvars? I would say half an hour is a good throughput.

Are there any indexes on both actvars.product_level and prodlevel.code_level? 
Are they exactly compatible type? int2 and int4 are not compatible in postgresql 
lingo.

That plan should go for index scan. Can you show us the table definitions?

And yes, what tuning you did to postgresql?

  Shridhar




view thread (7+ messages)  latest in thread

Message-ID: <3F73E9DB.80104@persistent.co.in>
Permalink:  ../3F73E9DB.80104@persistent.co.in/
Also on:    postgresql.org/message-id/3F73E9DB.80104@persistent.co.in

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: shridhar_daithankar@persistent.co.in, rantunes@student.dei.uc.pt
  Subject: Re: Indices arent being used
  In-Reply-To: <3F73E9DB.80104@persistent.co.in>

* 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