pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedOptimizer differences between 7.2 and 7.3
4+ messages / 4 participants
[nested] [flat]
* Optimizer differences between 7.2 and 7.3
@ 2003-07-07 14:17 Jeff Boes <jboes@nexcerpt.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jeff Boes @ 2003-07-07 14:17 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer differences between 7.2 and 7.3
@ 2003-07-07 14:40 Richard Huxton <dev@archonet.com>
parent: Jeff Boes <jboes@nexcerpt.com>
0 siblings, 1 reply; 4+ messages in thread
From: Richard Huxton @ 2003-07-07 14:40 UTC (permalink / raw)
To: Jeff Boes <jboes@nexcerpt.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer differences between 7.2 and 7.3
@ 2003-07-21 08:25 SZUCS Gábor <surrano@mailbox.hu>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 4+ messages in thread
From: SZUCS Gábor @ 2003-07-21 08:25 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Optimizer differences between 7.2 and 7.3
@ 2003-07-21 13:38 Tom Lane <tgl@sss.pgh.pa.us>
parent: SZUCS Gábor <surrano@mailbox.hu>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2003-07-21 13:38 UTC (permalink / raw)
To: SZUCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-performance
=?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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2003-07-21 13:38 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-07-07 14:17 Optimizer differences between 7.2 and 7.3 Jeff Boes <jboes@nexcerpt.com>
2003-07-07 14:40 ` Richard Huxton <dev@archonet.com>
2003-07-21 08:25 ` SZUCS Gábor <surrano@mailbox.hu>
2003-07-21 13:38 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox