Strange query plan

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 FTSAnyone 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
http://www.gowindowslive.com/summergames?ocid=TXT_TAGHM

This may be a long shot... But I had a slow query once on a large table
because the query plan was doing a sequential scan, even after analyze.
I set "default_statistics_target" to 1000 (in postgres.conf), rebooted
and reanalyzed. A much better query plan was developed as a result and
the query was fast.

________________________________

From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Martin Gainty
Sent: Thursday, August 14, 2008 7:57 AM
To: Dmitry Teslenko; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Strange query plan

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.
<http://www.gowindowslive.com/summergames?ocid=TXT_TAGHM&gt;

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

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

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

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.