Re: Optimizer differences between 7.2 and 7.3

On Monday 07 Jul 2003 3:17 pm, Jeff Boes wrote:
> Our production database is running under 7.2.4; our test database
> running almost the same data is at 7.3.3.  One table has about 400,000
> rows in each schema. A query against an indexed column uses an index
> scan under 7.2.4, but a sequential scan under 7.3.3. A count of the
> table in question shows that they have comparable numbers of matching
> rows.
[snip[
> select count(*) from articles
>  where path_base like 'http://news.findlaw.com/hdocs%';
>  count
> -------
>     38
[snip]
> I can't find any differences between the indexes (ix_articles_3 exists
> in both schemas); the column statistics are set up the same (the
> default); and the optimizer settings (costs in postgresql.conf) are the
> same.

Check the locale the database was initdb'd to. You'll probably find 7.2.4 is
in the "C" locale whereas 7.3.3 isn't. The "like" comparison can only use
indexes in the "C" locale. I believe you might need to initdb again to fix
this.

--
  Richard Huxton