Re: postgres 8.2 seems to prefer Seq Scan

On 4/6/07, Michael Fuhr <mike(at)fuhr(dot)org> wrote:
> 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?

They should be although it's possible one of my co-workers updated one
of the DB's since I last dumped it, but should be a negligible amount
of data. Not sure of the exact version of 7.4; psql just says:
psql --version
psql (PostgreSQL) 7.4
contains support for command-line editing

8.2 is 8.2.3

>
> Have you analyzed all tables in both versions? The row count
> estimate in 7.4 is much closer to reality than in 8.2:
>

Yes.

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

Thanks! I'll give that a try and report back.

Alex