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

Re: seqscan instead of index scan

From: "Merlin Moncure" <merlin(dot)moncure(at)rcsonline(dot)com>
To: "Martin Sarsale" <martin(at)emepe3(dot)net>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: seqscan instead of index scan
Date: 2004-08-30 18:29:24
Message-ID: 6EE64EF3AB31D5448D0007DD34EEB3412A748E@Herge.rcsinc.local (view raw or flat)
Thread:
Lists: pgsql-performance
> On Mon, 2004-08-30 at 15:06, Merlin Moncure wrote:
> > create function is_somethingable (ctype, dtype) returns boolean as
> 
> Thanks, but I would prefer a simpler solution.
> 
> I would like to know why this uses a seqscan instead of an index scan:
> 
> create index t_idx on t((c+d));
> select * from t where c+d > 0;
> 

hmmm, please define simple.  

Using a functional index you can define an index around the way you
access the data.  There is no faster or better way to do it...this is a
mathematical truth, not a problem with the planner.  Why not use the
right tool for the job?  A boolean index is super-efficient both in disk
space and cache utilization.

Multiple column indexes are useless for 'or' combinations! (however they
are a huge win for 'and' combinations because you don't have to merge).

With an 'or' expression, the planner must use one index or the other, or
use both and merge the results.  When and what the planner uses is an
educated guess based on statistics.

Also, your function can be changed...why fill all your queries with
Boolean cruft when you can abstract it into the database and reap the
speed savings at the same time?  I think it's time to rethink the
concept of 'simple'.

Constructive criticism all,
Merlin



Responses

pgsql-performance by date

Next:From: Pierre-Frédéric CaillaudDate: 2004-08-30 19:17:06
Subject: Re: Why does a simple query not use an obvious index?
Previous:From: Martin SarsaleDate: 2004-08-30 18:17:29
Subject: Re: seqscan instead of index scan

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