Обсуждение: Optimizer differences between 7.2 and 7.3

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.

On 7.2.4:

select count(*) from articles;
 count
--------
 420213

select count(*) from articles
 where path_base like 'http://news.findlaw.com/hdocs%';
 count
-------
    38

(and it returns this nearly instantaneously)

explain select count(*) from articles
   where path_base like 'http://news.findlaw.com/hdocs%'

Aggregate  (cost=6.02..6.02 rows=1 width=0)
  ->  Index Scan using ix_articles_3 on articles  (cost=0.00..6.01
rows=1 width=0)

On 7.3.3:

select count(*) from articles;
 count
--------
 406319

select count(*) from articles
   where path_base like 'http://news.findlaw.com/hdocs%'
 count
-------
    23

(and it takes many seconds to return)

explain select count(*) from articles
   where path_base like 'http://news.findlaw.com/hdocs%'

 Aggregate  (cost=205946.65..205946.65 rows=1 width=0)
   ->  Seq Scan on articles  (cost=0.00..205946.65 rows=1 width=0)
         Filter: (path_base ~~ 'http://news.findlaw.com/hdocs%'::text)

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.

--
Jeff Boes                                      vox 269.226.9550 ext 24
Database Engineer                                     fax 269.349.9076
Nexcerpt, Inc.                                 http://www.nexcerpt.com
           ...Nexcerpt... Extend your Expertise

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

A bit OT:

do regex ops (~, ~*) use index scan in non-"C" locales? Is it worth to
convert LIKE to regex?

G.
------------------------------- cut here -------------------------------
----- Original Message -----
From: "Richard Huxton" <dev@archonet.com>
Sent: Monday, July 07, 2003 4:40 PM


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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faqs/FAQ.html

=?ISO-8859-1?Q?SZUCS_G=E1bor?= <surrano@mailbox.hu> writes:
> do regex ops (~, ~*) use index scan in non-"C" locales? Is it worth to
> convert LIKE to regex?

The locale issues are the same either way.

            regards, tom lane