pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: 장현성 <siche@siche.net>
To: pgsql-performance@postgresql.org
Subject: Using "LIMIT" is much faster even though, searching with PK.
Date: Wed, 01 Dec 2004 13:10:27 +0900
Message-ID: <41AD4433.1030909@siche.net> (raw)

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!




view thread (7+ messages)  latest in thread

Message-ID: <41AD4433.1030909@siche.net>
Permalink:  ../41AD4433.1030909@siche.net/
Also on:    postgresql.org/message-id/41AD4433.1030909@siche.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: siche@siche.net
  Subject: Re: Using "LIMIT" is much faster even though, searching with PK.
  In-Reply-To: <41AD4433.1030909@siche.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox