Re: Strange query plan

On 14 août 08, at 16:28, Dmitry Teslenko wrote:

> On Thu, Aug 14, 2008 at 17:55, Igor Neyman <ineyman@perceptron.com>
> wrote:
>>
>> -----Original Message-----
>> From: Dmitry Teslenko [mailto:dteslenko@gmail.com]
>> Sent: Thursday, August 14, 2008 6:57 AM
>> To: pgsql-general@postgresql.org
>> Subject: Strange query plan
>>
>> Hello!
>>
>> I have following table:
>>
>> CREATE TABLE table1 (
>>        field1 INTEGER NOT NULL,
>>        field2 INTEGER NOT NULL,
>>        field3 CHARACTER(30),
>>        ... some more numeric fields)
>>
>> I have also those indexes:
>>
>> CREATE UNIQUE INDEX idx1 ON table1 USING btree (field3, field2,
>> field1)
>> CREATE INDEX idx2 ON table1 USING btree (field1, field3)
>>
>> Then I query this table with something like this:
>>
>> SELECT SUM(...) FROM table1 WHERE field3 = 'ABC' AND field1 <> 1
>>        GROUP BY field2
>>
I just wonder if you should create your index in the other order, like

CREATE INDEX idx2 ON table1 USING btree (field3, field1)

Documentation @ http://www.postgresql.org/docs/8.3/interactive/
indexes-multicolumn.html says :

> The exact rule is that equality constraints on leading columns,
> plus any inequality constraints on the first column that does not
> have an equality constraint, will be used to limit the portion of
> the index that is scanned

Hope this helps

Tom


>> And planner picks up a sequential scan of a table. Why does he?
>>
>>
>> [I.N.]
>> How big is your table?
>> If it's not too big, the cost of table scan might be lower than using
>> index.
>>
>> Igor
>
> Table contains ~1 million rows and scan takes very long time. That's
> the reason I'm asking the question on a mail list.
>
> --
> A: Because it messes up the order in which people normally read text.
> Q: Why is top-posting such a bad thing?
> A: Top-posting.
> Q: What is the most annoying thing in e-mail?
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general