Re: q: explain analyze - Mailing list pgsql-general

From Tom Lane
Subject Re: q: explain analyze
Date
Msg-id 2515.1136921840@sss.pgh.pa.us
Whole thread Raw
In response to Re: q: explain analyze  (Mark <sendmailtomark@yahoo.com>)
List pgsql-general
Mark <sendmailtomark@yahoo.com> writes:
> I do have an index on amount, but I guess it won't be used for >= ...

The general rule is that an index is only helpful for extracting a
fairly small subset of the table ("small" can mean as little as 1%).
So a one-sided inequality is not usefully indexable unless the
comparison constant is near the end of the data range.  The planner
does understand this and will do the right things as long as the
ANALYZE statistics are reasonably accurate.

> is there any way to force usage of index?

You can try setting enable_seqscan = off, but you'll likely find
that the planner is making the right decision.  (If it isn't,
you may want to play with the value of random_page_cost ... but
be wary of changing that based on a small number of test cases.)

> Can I defined index for _NOT_EQUAL_ ?

No.  See above.

            regards, tom lane

pgsql-general by date:

Previous
From: Jerry LeVan
Date:
Subject: ANN: PgBrowser-1.6 and PgBrowser-1.7
Next
From: Jaime Casanova
Date:
Subject: Re: q: explain analyze