From: Michael Fuhr <mike@fuhr.org>
To: Alex Deucher <alexdeucher@gmail.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: postgres 8.2 seems to prefer Seq Scan
Date: Fri, 6 Apr 2007 15:31:47 -0600
Message-ID: <20070406213147.GA12788@winnie.fuhr.org> (raw)
In-Reply-To: <a728f9f90704061338l50b7daccuc14afa305c3489cc@mail.gmail.com>
References: <a728f9f90704061338l50b7daccuc14afa305c3489cc@mail.gmail.com>
On Fri, Apr 06, 2007 at 04:38:33PM -0400, Alex Deucher wrote:
> One more anomaly between 7.4 and 8.2. DB dumped from 7.4 and loaded
> onto 8.2, both have locale set to C. 8.2 seems to prefer Seq Scans
> for the first query while the ordering in the second query seems to
> perform worse on 8.2. I ran analyze. I've tried with the encoding
> set to UTF-8 and SQL_ASCII; same numbers and plans. Any ideas how to
> improve this?
Are you sure the data sets are identical? The 7.4 query returned
0 rows; the 8.2 query returned 1 row. If you're running the same
query against the same data in both versions then at least one of
them appears to be returning the wrong result. Exactly which
versions of 7.4 and 8.2 are you running?
Have you analyzed all tables in both versions? The row count
estimate in 7.4 is much closer to reality than in 8.2:
7.4
> -> Index Scan using pnum_idx on event (cost=0.00..3.37 rows=19
> width=172) (actual time=0.063..0.063 rows=0 loops=1)
> Index Cond: ((pnum)::text = 'AB5819188'::text)
8.2
> -> Index Scan using pnum_idx on event (cost=0.00..3147.63
> rows=1779 width=171) (actual time=0.030..0.033 rows=1 loops=1)
> Index Cond: ((pnum)::text = 'AB5819188'::text)
If analyzing the event table doesn't improve the row count estimate
then try increasing the statistics target for event.pnum and analyzing
again. Example:
ALTER TABLE event ALTER pnum SET STATISTICS 100;
ANALYZE event;
You can set the statistics target as high as 1000 to get more
accurate results at the cost of longer ANALYZE times.
--
Michael Fuhr
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: mike@fuhr.org, alexdeucher@gmail.com
Subject: Re: postgres 8.2 seems to prefer Seq Scan
In-Reply-To: <20070406213147.GA12788@winnie.fuhr.org>
* 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