On 14 août 08, at 16:28, Dmitry Teslenko wrote:
On Thu, Aug 14, 2008 at 17:55, Igor Neyman <ineyman@xxxxxxxxxxxxxx>
wrote:
-----Original Message-----
From: Dmitry Teslenko [mailto:dteslenko@xxxxxxxxx]
Sent: Thursday, August 14, 2008 6:57 AM
To: pgsql-general@xxxxxxxxxxxxxx
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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
