Thread: How to force PostgreSQL using an index

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA" style="font-size:
10.0pt;font-family:Arial">Is there a way to force PostgreSQL using an index for a SELECT statement?  I just want to
confirmthat the index PostgreSQL decides to use is better than the index I supposed PostgreSQL would use (I already
analyzethe table).</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial">Regards,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-CA"
style="font-size:
10.0pt;font-family:Arial">--</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-CA" style="font-size: 
10.0pt;font-family:Arial">Daniel CAUNE</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-CA" style="font-size: 
10.0pt;font-family:Arial">Ubisoft Online Technology</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font
face="Arial"size="2"><span lang="EN-CA" style="font-size: 
10.0pt;font-family:Arial">(514) 4090 2040 ext. 5418</span></font><span lang="EN-CA"></span><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span lang="EN-CA" style="font-size:12.0pt"> </span></font></div> 

SET SESSION enable_seqscan TO OFF could be interpreted as a strong hint to the server that you want it to use indexes.
It'snot completely mandatory (the server WILL still do a sequential scan if it has to) but postgresql will strongly
preferindex scans.  You may also have some luck twiddling the cpu_index_tuple_cost option. 

- Owen

(Apologies for the Outlookism.)

-----Original Message-----
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Daniel Caune
Sent: Wednesday, February 15, 2006 1:59 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] How to force PostgreSQL using an index


Hi,
Is there a way to force PostgreSQL using an index for a SELECT statement?  I just want to confirm that the index
PostgreSQLdecides to use is better than the index I supposed PostgreSQL would use (I already analyze the table). 
Regards,
--
Daniel CAUNE
Ubisoft Online Technology
(514) 4090 2040 ext. 5418

On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> Hi,
> 
>  
> 
> Is there a way to force PostgreSQL using an index for a SELECT
> statement?  I just want to confirm that the index PostgreSQL decides to
> use is better than the index I supposed PostgreSQL would use (I already
> analyze the table).

Your best bet is to do 

set enable_indexscan=false;

and then do the EXPLAIN ANALYSE for your select.

You might also find that fiddling with other settings affects the
planner's idea of what would be a good plan.  The planner is
sensitive to what it thinks it knows about your environment.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
If they don't do anything, we don't need their acronym.    --Josh Hamilton, on the US FEMA

> On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> > Hi,
> >
> >
> >
> > Is there a way to force PostgreSQL using an index for a SELECT
> > statement?  I just want to confirm that the index PostgreSQL decides
to
> > use is better than the index I supposed PostgreSQL would use (I
already
> > analyze the table).
>
> Your best bet is to do
>
> set enable_indexscan=false;
>
> and then do the EXPLAIN ANALYSE for your select.
>
> You might also find that fiddling with other settings affects the
> planner's idea of what would be a good plan.  The planner is
> sensitive to what it thinks it knows about your environment.
>

I see, but that doesn't explain whether it is possible to specify the
index to use.  It seems that those options just force PostgreSQL using
another plan.

For example, I have a table that contains historical data from which I
try to get a subset for a specified period of time:

SELECT <some-columns> FROM GSLOG_EVENT WHERE EVENT_NAME = 'player-status-update'   AND EVENT_DATE_CREATED >=
<start-time>  AND EVENT_DATE_CREATED < <end-time> 

I have an index on EVENT_DATE_CREATED that does it job.  But I though
that I can help my favourite PostgreSQL if I create a composite index on
EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
is more dense that EVENT_NAME).

PostgreSQL prefer the simple index rather than the composite index (for
I/O consideration, I suppose).  I wanted to know how bad the composite
index would be if it was used (the estimate cost).


Daniel

Daniel Caune wrote:
>
> Andrew Sullivan wrote:
>
> > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> >
> > >
> > > Is there a way to force PostgreSQL using an index for a SELECT
> > > statement?
> >
> > Your best bet is to do
> >
> > set enable_indexscan=false;
> >
> > and then do the EXPLAIN ANALYSE for your select.
>
> I see, but that doesn't explain whether it is possible to specify the
> index to use.  It seems that those options just force PostgreSQL using
> another plan.

(snip)

> I have an index on EVENT_DATE_CREATED that does it job.  But I though
> that I can help my favourite PostgreSQL if I create a
> composite index on
> EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
> is more dense that EVENT_NAME).
>
> PostgreSQL prefer the simple index rather than the composite index (for
> I/O consideration, I suppose).  I wanted to know how bad the composite
> index would be if it was used (the estimate cost).

Drop the simple index and re-create it when you're done?

As I understand it, the problem with letting clients specify which indexes to use is that they tend, on the whole, to
bewrong about what's most efficient, so it's a feature almost specifically designed for shooting yourself in the foot
with. I agree that it'd be useful for experimenting with indexing schemes, but then, so is DROP INDEX. 

-Owen

On Wed, Feb 15, 2006 at 05:26:57PM -0500, Daniel Caune wrote:
> I have an index on EVENT_DATE_CREATED that does it job.  But I though
> that I can help my favourite PostgreSQL if I create a composite index on
> EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
> is more dense that EVENT_NAME).
> 
> PostgreSQL prefer the simple index rather than the composite index (for
> I/O consideration, I suppose).  I wanted to know how bad the composite
> index would be if it was used (the estimate cost).

You could do that by turning up the data the planner spits out, to
see why it picks this index.  It doesn't do so automatically, I
think: I _think_ it's cost based (Tom will probably chime in here and
remind me how little I know).  My bet is that the second column isn't
adding enough selectivity to help.

One thing that might affect this is to fiddle with the SET STATISTICS
settings on the column(s) in question.  You might find that as the
samples get better, your index turns out to be usefully selective,
and it gets chosen.

But to answer your question, no, you can't tell it "use index foo".

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.     --Dennis Ritchie

"Daniel Caune" <daniel.caune@ubisoft.com> writes:
> SELECT <some-columns>
>   FROM GSLOG_EVENT
>   WHERE EVENT_NAME = 'player-status-update'
>     AND EVENT_DATE_CREATED >= <start-time>
>     AND EVENT_DATE_CREATED < <end-time>

> I have an index on EVENT_DATE_CREATED that does it job.  But I though
> that I can help my favourite PostgreSQL if I create a composite index on
> EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
> is more dense that EVENT_NAME).

Wrong ... should be EVENT_NAME first.  Think about the sort order of the
data to see why --- your query represents a contiguous subset of the
index if EVENT_NAME is first, but not if EVENT_DATE_CREATED is first.
        regards, tom lane

> > > > Is there a way to force PostgreSQL using an index for a SELECT
> > > > statement?
> > >
> > > Your best bet is to do
> > >
> > > set enable_indexscan=false;
> > >
> > > and then do the EXPLAIN ANALYSE for your select.
> >
> > I see, but that doesn't explain whether it is possible to specify
the
> > index to use.  It seems that those options just force PostgreSQL
using
> > another plan.
>
> (snip)
>
> > I have an index on EVENT_DATE_CREATED that does it job.  But I
though
> > that I can help my favourite PostgreSQL if I create a
> > composite index on
> > EVENT_DATE_CREATED and EVENT_NAME (in that order as
EVENT_DATE_CREATED
> > is more dense that EVENT_NAME).
> >
> > PostgreSQL prefer the simple index rather than the composite index
(for
> > I/O consideration, I suppose).  I wanted to know how bad the
composite
> > index would be if it was used (the estimate cost).
>
> Drop the simple index and re-create it when you're done?
>

Yes, that is a solution!  I will try that! :-)

> As I understand it, the problem with letting clients specify which
indexes
> to use is that they tend, on the whole, to be wrong about what's most
> efficient, so it's a feature almost specifically designed for shooting
> yourself in the foot with.  I agree that it'd be useful for
experimenting
> with indexing schemes, but then, so is DROP INDEX.
>

Yes, indeed, such a feature could be badly used.  However it may happen
sometimes that the planner is wrong; I already encountered such
situations with both Oracle 9i and SQL Server 2000, even with statistics
calculated.  That is rare but that happens.  Such options /*+ <HINT> */
or WITH(INDEX(...)) help in such situations, even if that really sucks
for the reason you know.


Daniel

"Owen Jacobson" <ojacobson@osl.com> writes:
> On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
>> I see, but that doesn't explain whether it is possible to specify the
>> index to use.  It seems that those options just force PostgreSQL using
>> another plan.

> Drop the simple index and re-create it when you're done?

BTW, the cute way to do that is
BEGIN;DROP INDEX unwanted;EXPLAIN ANALYZE whatever...;ROLLBACK;

No need to actually rebuild the index when you are done.

This does hold an exclusive lock on the table for the duration of your
experiment, so maybe not such a good idea in a live environment ... but
then again, dropping useful indexes in a live environment isn't a good
idea either, and this at least reduces the duration of the experiment by
a good deal.
        regards, tom lane

> "Owen Jacobson" <ojacobson@osl.com> writes:
> > On Wed, Feb 15, 2006 at 04:58:54PM -0500, Daniel Caune wrote:
> >> I see, but that doesn't explain whether it is possible to specify
the
> >> index to use.  It seems that those options just force PostgreSQL
using
> >> another plan.
>
> > Drop the simple index and re-create it when you're done?
>
> BTW, the cute way to do that is
>
>     BEGIN;
>     DROP INDEX unwanted;
>     EXPLAIN ANALYZE whatever...;
>     ROLLBACK;
>
> No need to actually rebuild the index when you are done.
>
> This does hold an exclusive lock on the table for the duration of your
> experiment, so maybe not such a good idea in a live environment ...
but
> then again, dropping useful indexes in a live environment isn't a good
> idea either, and this at least reduces the duration of the experiment
by
> a good deal.
>

Thanks, that's great!

> -----Message d'origine-----
> De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Envoyé : mercredi, février 15, 2006 17:47
> À : Daniel Caune
> Cc : Andrew Sullivan; pgsql-sql@postgresql.org
> Objet : Re: [SQL] How to force PostgreSQL using an index
>
> "Daniel Caune" <daniel.caune@ubisoft.com> writes:
> > SELECT <some-columns>
> >   FROM GSLOG_EVENT
> >   WHERE EVENT_NAME = 'player-status-update'
> >     AND EVENT_DATE_CREATED >= <start-time>
> >     AND EVENT_DATE_CREATED < <end-time>
>
> > I have an index on EVENT_DATE_CREATED that does it job.  But I though
> > that I can help my favourite PostgreSQL if I create a composite index on
> > EVENT_DATE_CREATED and EVENT_NAME (in that order as EVENT_DATE_CREATED
> > is more dense that EVENT_NAME).
>
> Wrong ... should be EVENT_NAME first.  Think about the sort order of the
> data to see why --- your query represents a contiguous subset of the
> index if EVENT_NAME is first, but not if EVENT_DATE_CREATED is first.
>
>             regards, tom lane

Yes, you're right!