Re: Strange query plan

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(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Martin Gainty
Sent: Thursday, August 14, 2008 7:57 AM
To: Dmitry Teslenko; pgsql-general(at)postgresql(dot)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(at)gmail(dot)com
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)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>