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
