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(at)perceptron(dot)com>
> wrote:
>>
>> -----Original Message-----
>> From: Dmitry Teslenko [mailto:dteslenko(at)gmail(dot)com]
>> Sent: Thursday, August 14, 2008 6:57 AM
>> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general