Re: Strange query plan

Try this...

Set default_statistics_target to be 1000 in postgres.conf then reboot
your pg server. "Analyze" the table. Try the query again.

If that fails, drop the index on (field1, field3) and recreate the other
way around (field3, field1). Analyze again and try the query.

-dave

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Dmitry Teslenko
Sent: Thursday, August 14, 2008 10:29 AM
To: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Strange query plan

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
>
> 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?