pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Artimenko Igor <igorart7@yahoo.com>
To: Dennis Bjorklund <db@zigo.dhs.org>
Subject: Re: Why those queries do not utilize indexes?
Date: Fri, 27 Aug 2004 14:08:58 -0700 (PDT)
Message-ID: <20040827210858.16196.qmail@web11904.mail.yahoo.com> (raw)
In-Reply-To: <Pine.LNX.4.44.0408272146260.9559-100000@zigo.dhs.org>

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
scan.

Igor Artimenko

--- Dennis Bjorklund <db@zigo.dhs.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 Björklund
> 
> 



		
_______________________________
Do you Yahoo!?
Win 1 of 4,000 free domain names from Yahoo! Enter now.
http://promotions.yahoo.com/goldrush



view thread (4+ messages)  latest in thread

Message-ID: <20040827210858.16196.qmail@web11904.mail.yahoo.com>
Permalink:  ../20040827210858.16196.qmail@web11904.mail.yahoo.com/
Also on:    postgresql.org/message-id/20040827210858.16196.qmail@web11904.mail.yahoo.com

 · 

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: igorart7@yahoo.com, db@zigo.dhs.org
  Subject: Re: Why those queries do not utilize indexes?
  In-Reply-To: <20040827210858.16196.qmail@web11904.mail.yahoo.com>

* 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