Skip site navigation (1) Skip section navigation (2)

Re: Date index not used when selecting a date range

From: Poul Møller Hansen <freebsd(at)pbnet(dot)dk>
To: "Hoover, Jeffrey" <jhoover(at)jcvi(dot)org>, pgsql-general(at)postgresql(dot)org
Subject: Re: Date index not used when selecting a date range
Date: 2008-07-29 07:08:49
Message-ID: 488EC201.5020803@pbnet.dk (view raw or flat)
Thread:
Lists: pgsql-general
> You can prevent postgres from using the index on node by changing the reference in the WHERE clause to an expression, like so:
>
> SELECT * FROM public.stat WHERE node||'' = '1010101010' AND
>  ("date" <= '2008-06-30'::date AND "date" >= '2008-01-01'::date);
>
> Perhaps this will lead the optimizer to choose the index on date. However, I have noticed reluctance in the postgres optimizer to use multi-column indexes, presumably because the increased size of the indexed values lowers expectations for performance of the index.
>
>
>   
I think you are right about the multi-column usage.
When I use node||'' instead of node, it will do a seq scan.

Poul


In response to

pgsql-general by date

Next:From: Craig RingerDate: 2008-07-29 07:11:56
Subject: Re: How to give input a file for a stored procedure
Previous:From: Klint GoreDate: 2008-07-29 05:17:40
Subject: Re: why can't I load pgxml.sql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group