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

Re: help needed -- sequential scan problem

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: sarlav kumar <sarlavk(at)yahoo(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: help needed -- sequential scan problem
Date: 2004-11-20 05:19:46
Message-ID: 16679.1100927986@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
sarlav kumar <sarlavk(at)yahoo(dot)com> writes:
> I have a query which does not use index scan unless I force postgres to use index scan. I dont want to force postgres, unless there is no way of optimizing this query.

The major issue seems to be in the sub-selects:

>                  ->  Seq Scan on merchant_purchase mp  (cost=0.00..95.39 rows=44 width=4) (actual time=2.37..2.58 rows=6 loops=619)
>                        Filter: (merchant_id = $0)

where the estimated row count is a factor of 7 too high.  If the
estimated row count were even a little lower, it'd probably have gone
for an indexscan.  You might get some results from increasing the
statistics target for merchant_purchase.merchant_id.  If that doesn't
help, I'd think about reducing random_page_cost a little bit.

			regards, tom lane

In response to

Responses

pgsql-performance by date

Next:From: Dawid KuroczkoDate: 2004-11-20 09:36:38
Subject: Re: tablespace + RAM disk?
Previous:From: David ParkerDate: 2004-11-20 04:18:51
Subject: Re: tablespace + RAM disk?

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