From: Gregory Stark <stark@enterprisedb.com>
To: Guillaume Cottenceau <gc@mnc.ch>
Cc: David West <david.west@cusppoint.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: limit clause breaks query planner?
Date: Tue, 02 Sep 2008 10:44:54 +0100
Message-ID: <87od363mqx.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <87prnmor13.fsf@mnc.ch>
References: <1683189597512580954@unknownmsgid>
<162867790809010552o624a347aif0eb5fc71e7bbad@mail.gmail.com>
<2934211014530751484@unknownmsgid>
<162867790809011217n587e6090qdec2c4c6a1f580e6@mail.gmail.com>
<48BCB90D.8090606@pws.com.au>
<87prnmor13.fsf@mnc.ch>
>>>> "Limit (cost=0.00..3.68 rows=15 width=128) (actual time=85837.043..85896.140 rows=15 loops=1)"
>>>> " -> Seq Scan on my_table this_ (cost=0.00..258789.88 rows=1055580 width=128) (actual time=85837.038..85896.091 rows=15 loops=1)"
>>>> " Filter: ((A IS NULL) AND ((B)::text = '21'::text))"
>>>> "Total runtime: 85896.214 ms"
Postgres does collect and use statistics about what fraction of the "A" column
is null. It also collects and uses statistics about what fraction of the "B"
column is 21 (using a histogram). And it does take the LIMIT into account.
I think the other poster might well be right about this table being extremely
bloated. You could test by running and posting the results of:
VACUUM VERBOSE my_table
What it doesn't collect is where in the table those records are -- so if there
are a lot of them then it might use a sequential scan regardless of whether
they're at the beginning or end of the table. That seems unlikely to be the
problem though.
The other thing it doesn't collect is how many of the B=21 records have null
As. So if a large percentage of the table has A as null then it will assume
that's true for the B=21 records and if there are a lot of B=21 records then
it will assume a sequential scan will find matches quickly. If in fact the two
columns are highly correlated and B=21 records almost never have A null
whereas records with other values of B have lots of null values then Postgres
might make a bad decision here.
Also, it only has the statitics for B=21 via a histogram. If the distribution
of B is highly skewed so that, for example values between 20 and 25 are very
common but B=21 happens to be quite rare then Postgres might get a bad
estimate here. You could improve this by raising the statistics target for the
B column and re-analyzing.
That brings up another question -- when was the last time this table was
analyzed?
What estimates and actual results does postgres get for simple queries like:
EXPLAIN ANALYZE SELECT count(*) FROM my_table WHERE A IS NULL;
EXPLAIN ANALYZE SELECT count(*) FROM my_table WHERE B=21;
EXPLAIN ANALYZE SELECT count(*) FROM my_table WHERE A IS NULL AND B=21;
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, gc@mnc.ch, david.west@cusppoint.com
Subject: Re: limit clause breaks query planner?
In-Reply-To: <87od363mqx.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox