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

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

Am Thursday, 14. August 2008 schrieb Dmitry Teslenko:
> 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?

Presumably because it thinks it is the best plan, and I see no reason to doubt
that outright.  You might get better performance with an index on field3.

On Thu, Aug 14, 2008 at 15:30, Peter Eisentraut <peter_e@gmx.net> wrote:
> Am Thursday, 14. August 2008 schrieb Dmitry Teslenko:
>> 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?
>
> Presumably because it thinks it is the best plan, and I see no reason to doubt
> that outright.  You might get better performance with an index on field3.
>

Why then idx2 on field1 and field3 don't help here?

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

the columns referenced in the predicate need to reference columns whichimplement indexes to avert FTS
Anyone else?
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> Date: Thu, 14 Aug 2008 14:57:09 +0400
> From: dteslenko@gmail.com
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] 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?
>
> --
> 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

---

Got Game? Win Prizes in the Windows Live Hotmail Mobile Summer Games Trivia Contest Find out how.

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

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

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

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

What does "explain analyze select.... (your query here)" have to say?

On Thu, Aug 14, 2008 at 18:47, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> What does "explain analyze select.... (your query here)" have to say?
>
Expalin analyze says it makes sequential scan on a table table1.

On Thu, Aug 14, 2008 at 18:48, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> 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
I've altered query and swapped field1 and field3 there, in where statement.
Seems that order doesn't matter.

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

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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dmitry Teslenko
Sent: Thursday, August 14, 2008 10:29 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Strange query plan

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

On Thu, Aug 14, 2008 at 8:48 AM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> Try this...
>
> Set default_statistics_target to be 1000 in postgres.conf then reboot
> your pg server.  "Analyze" the table.  Try the query again.

A reload is enough.  I think you might have to disconnect and
reconnect your client, but I'm not sure.