From: Ryszard Lach <siaco@autograf.pl>
To: pgsql-performance@postgresql.org
Subject: 7.3 vs 7.2 - different query plan, bad performance
Date: Fri, 13 Jun 2003 20:45:06 +0200
Message-ID: <20030613184506.GE21614@siaco.id.pl> (raw)
Hi.
I have a problem with performance after upgrading from 7.2 to 7.3. Let's
see two simple tables:
CREATE TABLE a (
id integer,
parent_id integer
);
with 1632 records, and
CREATE TABLE b (
id integer
);
with 5281 records, and a litle more complex view:
CREATE VIEW v_c AS
SELECT t1.id,
(SELECT count(*) AS count FROM a t3 WHERE (t3.parent_id = t2.id)) AS children_count
FROM (b t1 LEFT JOIN a t2 ON ((t1.id = t2.id)));
Now see the query run under explain analyze:
Postgresql 7.2:
siaco=# explain analyze select count(*) from v_c;
NOTICE: QUERY PLAN:
Aggregate (cost=219.66..219.66 rows=1 width=8) (actual time=162.75..162.75 rows=1 loops=1)
-> Merge Join (cost=139.66..207.16 rows=5000 width=8) (actual time=95.07..151.46 rows=5281 loops=1)
-> Sort (cost=69.83..69.83 rows=1000 width=4) (actual time=76.18..82.37 rows=5281 loops=1)
-> Seq Scan on b t1 (cost=0.00..20.00 rows=1000 width=4) (actual time=0.02..22.02 rows=5281 loops=1)
-> Sort (cost=69.83..69.83 rows=1000 width=4) (actual time=18.86..25.38 rows=5281 loops=1)
-> Seq Scan on a t2 (cost=0.00..20.00 rows=1000 width=4) (actual time=0.02..6.70 rows=1632 loops=1)
Total runtime: 164.34 msec
EXPLAIN
Postgresql 7.3:
siaco=# explain analyze select count(*) from v_c;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=224.66..224.66 rows=1 width=8) (actual time=5691.77..5691.77 rows=1 loops=1)
-> Subquery Scan v_c (cost=139.66..212.16 rows=5000 width=8) (actual time=24.72..5687.77 rows=5281 loops=1)
-> Merge Join (cost=139.66..212.16 rows=5000 width=8) (actual time=24.72..5681.55 rows=5281 loops=1)
Merge Cond: ("outer".id = "inner".id)
-> Sort (cost=69.83..72.33 rows=1000 width=4) (actual time=18.82..21.09 rows=5281 loops=1)
Sort Key: t1.id
-> Seq Scan on b t1 (cost=0.00..20.00 rows=1000 width=4) (actual time=0.01..7.28 rows=5281 loops=1)
-> Sort (cost=69.83..72.33 rows=1000 width=4) (actual time=4.74..7.15 rows=5281 loops=1)
Sort Key: t2.id
-> Seq Scan on a t2 (cost=0.00..20.00 rows=1000 width=4) (actual time=0.02..2.13 rows=1632 loops=1)
SubPlan
-> Aggregate (cost=22.51..22.51 rows=1 width=0) (actual time=1.07..1.07 rows=1 loops=5281)
-> Seq Scan on a t3 (cost=0.00..22.50 rows=5 width=0) (actual time=0.80..1.06 rows=1 loops=5281)
Filter: (parent_id = $0)
Total runtime: 5693.62 msec
(15 rows)
I can't understand where comes the big difference in query plan from, and
(that's more important) - how to force postgres 7.3 to execute it more
efficient?
Notice, that both databases on both machines are identical and machine with
postgres 7.3 is even faster than the other one.
Richard.
--
"First they ignore you. Then they laugh at you. Then they
fight you. Then you win." - Mohandas Gandhi.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: siaco@autograf.pl
Subject: Re: 7.3 vs 7.2 - different query plan, bad performance
In-Reply-To: <20030613184506.GE21614@siaco.id.pl>
References: <20030613184506.GE21614@siaco.id.pl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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