Thread: 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