Hi
Sorry, my previous post haven't shown in this list, so I repost this
one. I have a sql become very slow after upgrade to 8.4.5.
The table creation sql like this.
begin;
CREATE TABLE t_a (
id INT NOT NULL PRIMARY KEY
);
CREATE TABLE t_b (
id INT NOT NULL PRIMARY KEY
);
CREATE TABLE t_c (
id INT NOT NULL PRIMARY KEY,
flag boolean
);
INSERT
INTO t_a
SELECT s
FROM generate_series(1, 600) s;
INSERT
INTO t_b
SELECT s
FROM generate_series(1, 3000) s;
SELECT SETSEED(0.1);
INSERT
INTO t_c
SELECT s, RANDOM()> 0.5
FROM generate_series(1, 12000) s;
-- insert some id not in t_b into t_a
INSERT
INTO t_a values( 20000);
ANALYZE t_a;
ANALYZE t_b;
ANALYZE t_c;
end;
The query sql is like this.
SELECT t_a.id FROM t_a
WHERE EXISTS ( SELECT t_b.id FROM t_b, t_c
WHERE t_b.id = t_a.id AND t_c.flag = 'f')
I extract this part form a big query.I known this query is not very
good.The query plan is different between 8.1.10 and 8.4.5, 8.1.10 use
a index scan, 8.4.5 use two table scan.
PostgreSQL 8.1.10 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC)
3.4.4 (mingw special)
Seq Scan on t_a (cost=0.00..34.67 rows=300 width=4) (actual
time=0.025..5.350 rows=600 loops=1)
Filter: (subplan)
SubPlan
-> Nested Loop (cost=0.00..248.44 rows=6042 width=4) (actual
time=0.007..0.007 rows=1 loops=601)
-> Index Scan using t_b_pkey on t_b (cost=0.00..3.02
rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=601)
Index Cond: (id = $0)
-> Seq Scan on t_c (cost=0.00..185.00 rows=6042 width=0)
(actual time=0.001..0.001 rows=1 loops=600)
Filter: (NOT flag)
Total runtime: 5.574 ms
PostgreSQL 8.4.5, compiled by Visual C++ build 1400, 32-bit
Nested Loop Semi Join (cost=0.00..134044.44 rows=601 width=4) (actual
time=0.033..17375.045 rows=600 loops=1)
Join Filter: (t_a.id = t_b.id)
-> Seq Scan on t_a (cost=0.00..9.01 rows=601 width=4) (actual
time=0.008..0.172 rows=601 loops=1)
-> Nested Loop (cost=0.00..447282.00 rows=18126000 width=4)
(actual time=0.011..20.922 rows=30460 loops=601)
-> Seq Scan on t_c (cost=0.00..174.00 rows=6042 width=0)
(actual time=0.004..0.011 rows=11 loops=601)
Filter: (NOT flag)
-> Seq Scan on t_b (cost=0.00..44.00 rows=3000 width=4)
(actual time=0.004..0.652 rows=2756 loops=6642)
Total runtime: 17375.247 ms
If some t_a.id not in t_b.id 8.4.5 will become very slow. I confirmed
this behavior on default configuration.
Regards,
Yao
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
