Using "LIMIT" is much faster even though, searching with PK. - Mailing list pgsql-performance

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!