X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 91B883A541D for ; Wed, 1 Dec 2004 04:10:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 53749-10 for ; Wed, 1 Dec 2004 04:10:36 +0000 (GMT) Received: from fine4.fine.co.kr (unknown [211.238.40.4]) by svr1.postgresql.org (Postfix) with ESMTP id C066B3A543E for ; Wed, 1 Dec 2004 04:10:33 +0000 (GMT) Received: from [192.168.0.240] ([192.168.0.240]) (authenticated bits=0) by fine4.fine.co.kr (8.13.1/8.13.1) with ESMTP id iB14AOW7029197 for ; Wed, 1 Dec 2004 13:10:27 +0900 Message-ID: <41AD4433.1030909@siche.net> Date: Wed, 01 Dec 2004 13:10:27 +0900 From: =?UTF-8?B?7J6l7ZiE7ISx?= User-Agent: ¨È| eÈ 0.9 (Windows/20041103) X-Accept-Language: ko-kr, ko, en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org Subject: Using "LIMIT" is much faster even though, searching with PK. Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Amavis-Alert: BAD HEADER Non-encoded 8-bit data (char A8 hex) in message header 'User-Agent': User-Agent: \250\310| e\310 0.9 (Wind... X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200412/7 X-Sequence-Number: 9382 hello~ i'm curious about this situation. here is my test. my zipcode table has 47705 rows, and schema looks like this. pgsql=# \d zipcode Table "public.zipcode" Column | Type | Modifiers ---------+-----------------------+----------- zipcode | character(7) | not null sido | character varying(4) | not null gugun | character varying(13) | not null dong | character varying(43) | not null bunji | character varying(17) | not null seq | integer | not null Indexes: "zipcode_pkey" PRIMARY KEY, btree (seq) and I need seq scan so, pgsql=# SET enable_indexscan TO OFF; SET Time: 0.534 ms now test start! the first row. pgsql=# EXPLAIN ANALYZE select * from zipcode where seq = '1'; QUERY PLAN ------------------------------------------------------------------------------------------------------- Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=207) (actual time=0.029..88.099 rows=1 loops=1) Filter: (seq = 1) Total runtime: 88.187 ms (3 rows) Time: 89.392 ms pgsql=# the first row with LIMIT pgsql=# EXPLAIN ANALYZE select * from zipcode where seq = '1' LIMIT 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..1168.31 rows=1 width=207) (actual time=0.033..0.034 rows=1 loops=1) -> Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=207) (actual time=0.028..0.028 rows=1 loops=1) Filter: (seq = 1) Total runtime: 0.111 ms (4 rows) Time: 1.302 ms pgsql=# the last row, pgsql=# EXPLAIN ANALYZE select * from zipcode where seq = '47705'; QUERY PLAN ------------------------------------------------------------------------------------------------------- Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=207) (actual time=3.248..88.232 rows=1 loops=1) Filter: (seq = 47705) Total runtime: 88.317 ms (3 rows) Time: 89.521 ms pgsql=# the last row with LIMIT, pgsql=# EXPLAIN ANALYZE select * from zipcode where seq = '47705' LIMIT 1; QUERY PLAN ------------------------------------------------------------------------------------------------------------ Limit (cost=0.00..1168.31 rows=1 width=207) (actual time=3.254..3.254 rows=1 loops=1) -> Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=207) (actual time=3.248..3.248 rows=1 loops=1) Filter: (seq = 47705) Total runtime: 3.343 ms (4 rows) Time: 4.583 ms pgsql=# When I using index scan, the result was almost same, that means, there was no time difference, so i'll not mention about index scan. but, sequence scan, as you see above result, there is big time difference between using LIMIT and without using it. my question is, when we're searching with PK like SELECT * FROM table WHERE PK = 'xxx', we already know there is only 1 row or not. so, pgsql should stop searching when maching row was found, isn't it? i don't know exactly about mechanism how pgsql searching row its inside, so might be i'm thinking wrong way, any comments, advices, notes, anything will be appreciate to me!