My Server has 4GB mem and OS is Windows 2008 R2.
I downloaded the latest version,and the cost of "not in" is much higher than
that of "not exist".Please see attachment for detail.
As the time of query is very long,I didn't get the explain analyze result.
I think the id columns of table a and b are not null, so the query of "not in"
and "not exists" are equal,they should use similar plans.
I notice there is a "Materialize" step in my bad plans of "not in",but there
isn't in your following plan. I wonder how to get the plan you posted here, are
there any configure argument or command?
egress=# explain select max(a.info)from sli_test a where a.id not in(select
regress(# b.id from sli_test2 b where b.id<50000);
QUERY PLAN
---------------------------------------------------------------------------------
Aggregate (cost=38050.82..38050.83 rows=1 width=12)
-> Seq Scan on sli_test a (cost=18026.82..36800.82 rows=500000 width=12)
Filter: (NOT (hashed SubPlan 1))
SubPlan 1
-> Seq Scan on sli_test2 b (cost=0.00..17906.00 rows=48329 width=4)
Filter: (id < 50000)
(6 rows)
It runs in about 500ms here.
and I got following plan on kingbase 7.1,a modified version of postgres
product, no "Materialize"step either.
TEST=# explain select max(a.name)from a where a.id not in(select b.id from b
where b.id<50000);
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------
Aggregate (cost=72541.71..72541.72 rows=1 width=12)
-> Merge Anti Join (cost=0.00..70041.71 rows=1000000 width=12)
Merge Cond: (A.ID = B.ID)
-> Index Scan using A_PKEY on A (cost=0.00..32257.36 rows=1000000
width=16)
-> Index Scan using B_ID on B (cost=0.00..31117.69 rows=333333
width=4)
Index Cond: (ID < 50000)
(6 行)
时间: 20.095 ms
wget
http://get.enterprisedb.com/postgresql/postgresql-9.2.1-1-windows-x64-binaries.zip
initdb -D C:\Users\db2admin\Downloads\921win64\pgsql\data
CREATE DATABASE test_en
WITH OWNER =db2admin
TEMPLATE template0
ENCODING = 'SQL_ASCII'
TABLESPACE = pg_default
LC_COLLATE = 'C'
LC_CTYPE = 'C'
CONNECTION LIMIT = -1;
explain select max(a.name)from a where not exists(select 1 from b where
b.id<50000 and b.id=a.id);
explain analyze select max(a.name)from a where not exists(select 1 from b where
b.id<50000 and b.id=a.id);
Microsoft Windows [°æ±¾ 6.1.7601]
°æȨËùÓÐ (c) 2009 Microsoft Corporation¡£±£ÁôËùÓÐȨÀû¡£
C:\Users\db2admin>cd downloads\921win64\
C:\Users\db2admin\Downloads\921win64>cd pgsql\bin
C:\Users\db2admin\Downloads\921win64\pgsql\bin>initdb -D
C:\Users\db2admin\Downloads\921win64\pgsql\data
ÊôÓÚ´ËÊý¾Ý¿âϵͳµÄÎļþËÞÖ÷ΪÓû§ "db2admin".
´ËÓû§Ò²±ØÐëΪ·þÎñÆ÷½ø³ÌµÄËÞÖ÷.
The database cluster will be initialized with locale "Chinese
(Simplified)_People's Republic of China.936".
Encoding "GBK" implied by locale is not allowed as a server-side encoding.
The default database encoding will be set to "UTF8" instead.
initdb: could not find suitable text search configuration for locale "Chinese
(Simplified)_People's Republic of China.936"
ȱʡµÄÎı¾ËÑË÷ÅäÖý«»á±»ÉèÖõ½"simple"
´´½¨Ä¿Â¼ C:/Users/db2admin/Downloads/921win64/pgsql/data ... ³É¹¦
ÕýÔÚ´´½¨×ÓĿ¼ ... ³É¹¦
Ñ¡ÔñĬÈÏ×î´óÁª½ÓÊý (max_connections) ... 100
Ñ¡ÔñĬÈϹ²Ïí»º³åÇø´óС (shared_buffers) ... 32MB
´´½¨ÅäÖÃÎļþ ... ³É¹¦
ÔÚ C:/Users/db2admin/Downloads/921win64/pgsql/data/base/1 Öд´½¨ template1
Êý¾Ý¿â ... ³É¹¦
³õʼ»¯ pg_authid ... ³É¹¦
³õʼ»¯dependencies ... ³É¹¦
´´½¨ÏµÍ³ÊÓͼ ... ³É¹¦
ÕýÔÚ¼ÓÔØϵͳ¶ÔÏóÃèÊö ...³É¹¦
creating collations ... not supported on this platform
´´½¨×Ö·û¼¯×ª»» ... ³É¹¦
ÕýÔÚ´´½¨×Öµä ... ³É¹¦
¶ÔÄÚ½¨¶ÔÏóÉèÖÃȨÏÞ ... ³É¹¦
´´½¨ÐÅϢģʽ ... ³É¹¦
ÕýÔÚ×°ÔØPL/pgSQL·þÎñÆ÷¶Ë±à³ÌÓïÑÔ...³É¹¦
ÇåÀíÊý¾Ý¿â template1 ... ³É¹¦
¿½±´ template1 µ½ template0 ... ³É¹¦
¿½±´ template1 µ½ template0 ... ³É¹¦
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
³É¹¦. ÄúÏÖÔÚ¿ÉÒÔÓÃÏÂÃæµÄÃüÁîÔËÐÐÊý¾Ý¿â·þÎñÆ÷:
""postmaster -D "C:/Users/db2admin/Downloads/921win64/pgsql/data"
»òÕß
""pg_ctl -D "C:/Users/db2admin/Downloads/921win64/pgsql/data" -l logfile
start
C:\Users\db2admin\Downloads\921win64\pgsql\bin>pg_ctl -D
"C:/Users/db2admin/Downloads/921win64/pgsql/data" -l logfile start
server starting
C:\Users\db2admin\Downloads\921win64\pgsql\bin>psql postgres
psql (9.2.1)
ÊäÈë "help" À´»ñÈ¡°ïÖúÐÅÏ¢.
postgres=# CREATE DATABASE test_en
postgres-# WITH OWNER =db2admin
postgres-# TEMPLATE template0
postgres-# ENCODING = 'SQL_ASCII'
postgres-# TABLESPACE = pg_default
postgres-# LC_COLLATE = 'C'
postgres-# LC_CTYPE = 'C'
postgres-# CONNECTION LIMIT = -1;
CREATE DATABASE
postgres=# \c test_en
You are now connected to database "test_en" as user "db2admin".
test_en=# create table a(id int primary key,name varchar(10));
×¢Òâ: CREATE TABLE / PRIMARY KEY ½«ÒªÎª±í "a" ´´½¨Òþº¬Ë÷Òý "a_pkey"
CREATE TABLE
test_en=# create table b(id int not null,name varchar(10));
CREATE TABLE
test_en=# insert into a select
generate_series(1,1000000),'a'||generate_series(1,1000000);
INSERT 0 1000000
test_en=# insert into b select
generate_series(1,1000000),'b'||generate_series(1,1000000);
INSERT 0 1000000
test_en=# analyze a;
ANALYZE
test_en=# analyze b;
ANALYZE
test_en=# explain select max(a.name)from a where a.id not in(select b.id from b
where b.id<50000);
QUERY PLAN
-----------------------------------------------------------------------------
Aggregate (cost=9237419156.00..9237419156.01 rows=1 width=7)
-> Seq Scan on a (cost=0.00..9237417906.00 rows=500000 width=7)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..18350.20 rows=49840 width=4)
-> Seq Scan on b (cost=0.00..17906.00 rows=49840 width=4)
Filter: (id < 50000)
(7 ÐмǼ)
test_en=# explain select max(a.name)from a where not exists(select 1 from b
where b.id<50000 and b.id=a.id);
QUERY PLAN
---------------------------------------------------------------------------
Aggregate (cost=59554.42..59554.43 rows=1 width=7)
-> Hash Anti Join (cost=18724.00..57179.02 rows=950160 width=7)
Hash Cond: (a.id = b.id)
-> Seq Scan on a (cost=0.00..15406.00 rows=1000000 width=11)
-> Hash (cost=17906.00..17906.00 rows=49840 width=4)
-> Seq Scan on b (cost=0.00..17906.00 rows=49840 width=4)
Filter: (id < 50000)
(7 ÐмǼ)
test_en=#
test_en=# explain analyze select max(a.name)from a where not exists(select 1
from b where b.id<50000 and b.id=a.id);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=59554.42..59554.43 rows=1 width=7) (actual
time=1102.312..1102.312 rows=1 loops=1)
-> Hash Anti Join (cost=18724.00..57179.02 rows=950160 width=7) (actual
time=205.845..955.513 rows=950001 loops=1)
Hash Cond: (a.id = b.id)
-> Seq Scan on a (cost=0.00..15406.00 rows=1000000 width=11) (actual
time=0.010..227.189 rows=1000000 loops=1)
-> Hash (cost=17906.00..17906.00 rows=49840 width=4) (actual
time=178.182..178.182 rows=49999 loops=1)
Buckets: 4096 Batches: 2 Memory Usage: 883kB
-> Seq Scan on b (cost=0.00..17906.00 rows=49840 width=4)
(actual time=0.043..165.345 rows=49999 loops=1)
Filter: (id < 50000)
Rows Removed by Filter: 950001
Total runtime: 1102.617 ms
(10 ÐмǼ)
test_en=# create index idx_b_id on b(id);
CREATE INDEX
test_en=# analyze b;
ANALYZE
test_en=# explain select max(a.name)from a where a.id not in(select b.id from b
where b.id<50000);
QUERY PLAN
--------------------------------------------------------------------------------------------------
Aggregate (cost=1139330656.00..1139330656.01 rows=1 width=7)
-> Seq Scan on a (cost=0.00..1139329406.00 rows=500000 width=7)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..2153.20 rows=50171 width=4)
-> Index Only Scan using idx_b_id on b (cost=0.00..1706.34
rows=50171 width=4)
Index Cond: (id < 50000)
(7 ÐмǼ)
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
