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!
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
