Re: select distinct and index usage

From: "Stephen Denne" <Stephen(dot)Denne(at)datamail(dot)co(dot)nz>
To: "David Wilson" <david(dot)t(dot)wilson(at)gmail(dot)com>
Cc: <pgsql-general(at)postgresql(dot)org>
Subject: Re: select distinct and index usage
Date: 2008-04-08 01:11:50
Message-ID: F0238EBA67824444BC1CB4700960CB4805110074@dmpeints002.isotach.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

> Still doing the sequential scan on the table, but at least it's
> avoiding the expensive disk merge sort. It still seems as if I ought
> to be able to coax it into using an index for this type of query,
> though- especially since it's using one on the other table. Is there
> perhaps some way to reformulate the index in such a way as to make it
> more useful to the planner?

You're asking postgres to examine EVERY visible row (hence the sequential scan makes good sense), and tell you what field values there are.

You may be able to make use of an index by rearranging your query to generate a series between your min & max values, testing whether each value is in your table.

You've got 4252 distinct values, but what is the range of max - min? Say it's 5000 values, you'd do 5000 lookups via an index, unless postgres thought that the number of index based lookups where going to be more expensive than reading the entire table.

Regards,
Stephen Denne.

Disclaimer:
At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way.
__________________________________________________________________
This email has been scanned by the DMZGlobal Business Quality
Electronic Messaging Suite.
Please see http://www.dmzglobal.com/dmzmessaging.htm for details.
__________________________________________________________________

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Manuel Sugawara 2008-04-08 01:44:44 Cannot use a standalone backend to VACUUM in "postgres""
Previous Message Colin Wetherbee 2008-04-08 01:08:15 Re: Most Occurring Value