From: brad-pgperf@duttonbros.com
To: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: pgsql-performance@postgresql.org
Subject: Re: index usage
Date: Mon, 26 Apr 2004 12:16:28 -0700
Message-ID: <20040426191628.47296.qmail@uno.mnl.com> (raw)
In-Reply-To: <20040426115215.R11044@megazone.bigpanda.com>
References: <20040423222121.12061.qmail@uno.mnl.com>
<20040426115215.R11044@megazone.bigpanda.com>
When checking an index in postgres the original table has to be checked for
each result to find if the index entry is still valid? In which case you
can't blindly scan the whole index and assume the data is good. I was used
to Oracle behavior where the index is up to date so it can do the scan
without hitting the original table.
Does this sound correct to anyone?
Thanks,
Brad
Stephan Szabo writes:
> On Fri, 23 Apr 2004 brad-pgperf@duttonbros.com wrote:
>
>> I have a query which I think should be using an index all of the time but
>> postgres only uses the index part of the time. The index
>> (ticket_crm_map_crm_id_suppid) has the where clause column (crm_id) listed
>> first followed by the selected column (support_person_id). Wouldn't the
>> most efficient plan be to scan the index each time because the only columns
>> needed are in the index? Below is the table, 2 queries showing the
>
> Not necessarily. The rows in the actual file still need to be checked to
> see if they're visible to the select and if it's expected that the entire
> file (or a reasonable % of the pages anyway) will need to be loaded using
> the index isn't necessarily a win.
>
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: brad-pgperf@duttonbros.com, sszabo@megazone.bigpanda.com
Subject: Re: index usage
In-Reply-To: <20040426191628.47296.qmail@uno.mnl.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