I could force Postgres to use the best index by removing condition "msgstatus = CAST( 0 AS
smallint );" from WHERE clause & set enable_seqscan to off;
Total runtime in this case dropped from 1883 ms ( sequential reads ) to 1.598 ms ( best index ).
But unfortunatelly It does not resolve my problem. I can not remove above condition. I need to
find a way to use whole condition "WHERE user_id = CAST( 20000 AS BIGINT ) and msgstatus = CAST( 0
AS smallint );" and still utilyze index.
Yes you are right. Using "messagesStatus" index is even worse for my data set then sequential
--- Dennis Bjorklund <db(at)zigo(dot)dhs(dot)org> wrote:
> On Fri, 27 Aug 2004, Artimenko Igor wrote:
> > 1. Sequential search and very high cost if set enable_seqscan to on;
> > Seq scan on messageinfo ( cost=0.00..24371.30, rows =36802 )
> > 2. Index scan but even bigger cost if set enable_seqscan to off;
> > Index messagesStatus on messageinfo ( Cost=0.00..27220.72, rows=36802 )
> So pg thinks that a sequential scan will be a little bit faster (The cost
> is a little bit smaller). If you compare the actual runtimes maybe you
> will see that pg was right. In this case the cost is almost the same so
> the runtime is probably almost the same.
> When you have more data pg will start to use the index since then it will
> be faster to use an index compared to a seq. scan.
> /Dennis Bjrklund
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
In response to
pgsql-performance by date
|Next:||From: Gaetano Mendola||Date: 2004-08-28 01:02:47|
|Subject: Re: Equivalent praxis to CLUSTERED INDEX?|
|Previous:||From: Vivek Khera||Date: 2004-08-27 20:34:29|
|Subject: Re: Anyone familiar with Apple Xserve RAID|