X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by svr1.postgresql.org (Postfix) with ESMTP id EE1C8B43175 for ; Mon, 7 Jul 2003 14:17:54 +0000 (GMT) Received: from svr1.postgresql.org ([64.117.224.193]) by localhost (svr1.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 91864-05 for ; Mon, 7 Jul 2003 11:17:35 -0300 (ADT) Received: from yapok.nexcerpt.com (mail.nexcerpt.com [64.147.202.2]) by svr1.postgresql.org (Postfix) with ESMTP id BC86EB43376 for ; Mon, 7 Jul 2003 11:17:33 -0300 (ADT) Received: from takin.private.nexcerpt.com (zorro.nexcerpt.com [64.147.202.1]) by yapok.nexcerpt.com (8.11.6/8.11.6) with ESMTP id h67EHZ725991 for ; Mon, 7 Jul 2003 10:17:35 -0400 Subject: Optimizer differences between 7.2 and 7.3 From: Jeff Boes To: pgsql-performance@postgresql.org Content-Type: text/plain Organization: Nexcerpt, Inc. Message-Id: <1057587455.6466.26.camel@takin.private.nexcerpt.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.0 Date: 07 Jul 2003 10:17:35 -0400 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200307/129 X-Sequence-Number: 2422 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