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

Re: Query that does not use indexes

From: Ragnar <gnari(at)hive(dot)is>
To: Denis Gasparin <denis(at)edistar(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Query that does not use indexes
Date: 2007-03-28 10:11:42
Message-ID: 1175076702.1153.80.camel@localhost.localdomain (view raw or flat)
Thread:
Lists: pgsql-general
On þri, 2007-03-27 at 12:54 +0200, Denis Gasparin wrote:
> I have a query that performs a multiple join between four tables and
> that doesn't use the defined indexes.

> If I set enable_seqscan to off, the query obviously uses the indexes and
> it is considerable faster than normal planned execution with
> enable_seqscan=true.


a few things you might try:
fiddle with random_page_cost
make sure effective_cache_size is set to a realistic value
increase statistics target on critical columns, like 
  ORDER.ORDER_DATE and join columns
 (and re-analyze)

possibly get change the cast in your where clause:
  ORDER.ORDER_DATE::date between '2007-03-01' and '2007-03-27'
to
  ORDER.ORDER_DATE between '2007-03-01'::timestamp 
                   and '2007-03-27'::timestamp


gnari




In response to

pgsql-general by date

Next:From: Dmitry KoterovDate: 2007-03-28 10:12:43
Subject: Re: Strange behaviour under heavy load
Previous:From: Stephane BortzmeyerDate: 2007-03-28 10:07:25
Subject: [ANNOUNCE] echoping 6, with a PostgreSQL plugin

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