From: Christopher Kings-Lynne <chriskl@familyhealth.com.au>
To: Artimenko Igor <igorart7@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Why those queries do not utilize indexes?
Date: Sat, 28 Aug 2004 16:50:26 +0800
Message-ID: <41304752.3050208@familyhealth.com.au> (raw)
In-Reply-To: <20040827192911.72205.qmail@web11905.mail.yahoo.com>
References: <20040827192911.72205.qmail@web11905.mail.yahoo.com>
First things first: try vacuum full analyze on all the tables involved.
> 1. Should I afraid of high cost indexes? Or query will still be very efficient?
Not necessarily. However, EXPLAIN output is pretty much useless for us
for helping you. You need to post EXPLAIN ANALYZE output.
Then, you need to use explain analyze to check the speed difference
between the index and seq scan versions. Is the seq scan actually slower?
> 2. Postgres does not use the index I need. For my data sets it’s always msgstatus index is
> narrowest compare with ‘messagesStatus’ one. Is any way to “enforce” to use a particular index?
> What’s the logic when Postgres chooses one index compare with the other.
It's complicated, but it's based on teh statistics in pg_statistic that
the vacuum analyze command gathers.
> 3. I can change db structure to utilize Postgres specifics if you can tell them to me.
I avoid using int8 and int2 in the first place :) In PostgreSQL 8.0,
they will be less troublesome, but I've never seen a need for them!
> 4. Also, originally I had “messagesStatus” index having 2 components ( “msgstatus”, “user_id” ).
> But query SELECT * FROM messageinfo WHERE msgstatus = 0 did not utilize indexes in this case. It
> only worked if both index components are in WHERE part. So I have to remove 2-nd component
> “user_id” from messagesStatus index even I wanted it. Is any way that where clause has only 1-st
> component but index is utilized?
So long as your where clause matches a subset of the columns in the
index in left to right order, the index can be used. For example, if
your index is over (a, b, c) then select * where a=1 and b=2; can use
the index.
Chris
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: chriskl@familyhealth.com.au, igorart7@yahoo.com
Subject: Re: Why those queries do not utilize indexes?
In-Reply-To: <41304752.3050208@familyhealth.com.au>
References: <20040827192911.72205.qmail@web11905.mail.yahoo.com> <41304752.3050208@familyhealth.com.au>
* 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