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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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