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 555573A54B2 for ; Wed, 1 Dec 2004 06:03:45 +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 85154-04 for ; Wed, 1 Dec 2004 06:03:42 +0000 (GMT) Received: from fine4.fine.co.kr (unknown [211.238.40.4]) by svr1.postgresql.org (Postfix) with ESMTP id DAAF43A5492 for ; Wed, 1 Dec 2004 06:03:40 +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 iB163RCg032253; Wed, 1 Dec 2004 15:03:27 +0900 Message-ID: <41AD5EB3.80201@siche.net> Date: Wed, 01 Dec 2004 15:03:31 +0900 From: "Hyun-Sung, Jang" User-Agent: ¨È| eÈ 0.9 (Windows/20041103) X-Accept-Language: ko-kr, ko, en-us, en MIME-Version: 1.0 To: Josh Berkus Cc: pgsql-performance@postgresql.org, Tom Lane Subject: Re: Using "LIMIT" is much faster even though, searching References: <41AD4433.1030909@siche.net> <1390.1101875171@sss.pgh.pa.us> <41AD4AD0.908@siche.net> <200411302103.51643.josh@agliodbs.com> In-Reply-To: <200411302103.51643.josh@agliodbs.com> Content-Type: multipart/alternative; boundary="------------000201030000080003040402" 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.172 tagged_above=0 required=5 tests=AWL, HTML_10_20, HTML_MESSAGE X-Spam-Level: X-Archive-Number: 200412/12 X-Sequence-Number: 9387 This is a multi-part message in MIME format. --------------000201030000080003040402 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit do you need all of verbose information?? VACUUM FULL ANALYZE VERBOSE give me a lot of infomation, so i just cut zipcode parts. ==start=============================================================================== INFO: vacuuming "public.zipcode" INFO: "zipcode": found 0 removable, 47705 nonremovable row versions in 572 pages DETAIL: 0 dead row versions cannot be removed yet. Nonremovable row versions range from 76 to 136 bytes long. There were 0 unused item pointers. Total free space (including removable row versions) is 27944 bytes. 0 pages are or will become empty, including 0 at the end of the table. 91 pages containing 8924 free bytes are potential move destinations. CPU 0.03s/0.00u sec elapsed 0.03 sec. INFO: index "zipcode_pkey" now contains 47705 row versions in 147 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.01s/0.00u sec elapsed 0.00 sec. INFO: "zipcode": moved 0 row versions, truncated 572 to 572 pages DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: analyzing "public.zipcode" INFO: "zipcode": scanned 572 of 572 pages, containing 47705 live rows and 0 dead rows; 3000 rows in sample, 47705 estimated total rows INFO: free space map: 108 relations, 128 pages stored; 1760 total pages needed DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 182 kB shared memory. VACUUM pgsql=# ==end=============================================================================== USING INDEX SCAN ==start=============================================================================== pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Index Scan using zipcode_pkey on zipcode (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.058 rows=1 loops=1) Index Cond: (seq = 1) Total runtime: 0.152 ms (3 rows) pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1' LIMIT 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..3.02 rows=1 width=55) (actual time=0.059..0.060 rows=1 loops=1) -> Index Scan using zipcode_pkey on zipcode (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.054 rows=1 loops=1) Index Cond: (seq = 1) Total runtime: 0.158 ms (4 rows) pgsql=# WHEN SELECT LAST ROW ----- pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705'; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------- Index Scan using zipcode_pkey on zipcode (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.059 rows=1 loops=1) Index Cond: (seq = 47705) Total runtime: 0.150 ms (3 rows) pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705' LIMIT 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------- Limit (cost=0.00..3.02 rows=1 width=55) (actual time=0.057..0.057 rows=1 loops=1) -> Index Scan using zipcode_pkey on zipcode (cost=0.00..3.02 rows=1 width=55) (actual time=0.052..0.052 rows=1 loops=1) Index Cond: (seq = 47705) Total runtime: 0.156 ms (4 rows) pgsql=# ==end=============================================================================== USING SEQUENCE SCAN ==start=============================================================================== pgsql=# set enable_indexscan to off; SET pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1'; QUERY PLAN ------------------------------------------------------------------------------------------------------- Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=55) (actual time=0.032..109.934 rows=1 loops=1) Filter: (seq = 1) Total runtime: 110.021 ms (3 rows) pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1' LIMIT 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Limit (cost=0.00..1168.31 rows=1 width=55) (actual time=0.035..0.035 rows=1 loops=1) -> Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=55) (actual time=0.030..0.030 rows=1 loops=1) Filter: (seq = 1) Total runtime: 0.113 ms (4 rows) pgsql=# WHEN SELECT 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=55) (actual time=4.048..110.232 rows=1 loops=1) Filter: (seq = 47705) Total runtime: 110.322 ms (3 rows) pgsql=# pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705' LIMIT 1; QUERY PLAN ----------------------------------------------------------------------------------------------------------- Limit (cost=0.00..1168.31 rows=1 width=55) (actual time=4.038..4.038 rows=1 loops=1) -> Seq Scan on zipcode (cost=0.00..1168.31 rows=1 width=55) (actual time=4.033..4.033 rows=1 loops=1) Filter: (seq = 47705) Total runtime: 4.125 ms (4 rows) pgsql=# ==end=============================================================================== I just choose zipcode table for this test. not only zipcode table but other table also give me same result. SELECT * FROM table_name WHERE PK = 'xxx' was always slower than SELECT * FROM table_name WHERE PK = 'xxx' LIMIT 1 when sequence scan . i think pgsql tring to find more than 1 row when query executed even if searching condition is primary key. ah, why i'm using sequence as PK instead of zip code is in korea, the small towns doesn't have it's own zipcode so they share other big city's. that's why zip code can't be a primary key. actually, i'm not using sequence to find zipcode. i made it temporary for this test. i think there is nobody want to using sequence number to find zipcode, instead of city name. :-) Josh Berkus 쓴 글: >Hyun-Sang, > > > >>before test, I already executed VACUUM FULL. >>this result show up after vacuum full. >> >> > >Really? Your results really look like a bloated table. Can you run VACUUM >FULL ANALYZE VERBOSE on the table and post the output? > > > >>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. >> >> > >Can we see an index scan plan anyway? EXPLAIN ANALYZE? > >Oh, and if this is a zip codes table, why are you using a sequence as the >primary key instead of just using the zip code? > > > --------------000201030000080003040402 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit do you need all of verbose information??
VACUUM FULL ANALYZE VERBOSE give me a lot of infomation,
so i just cut zipcode parts.

==start===============================================================================
INFO:  vacuuming "public.zipcode"
INFO:  "zipcode": found 0 removable, 47705 nonremovable row versions in 572 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 76 to 136 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 27944 bytes.
0 pages are or will become empty, including 0 at the end of the table.
91 pages containing 8924 free bytes are potential move destinations.
CPU 0.03s/0.00u sec elapsed 0.03 sec.
INFO:  index "zipcode_pkey" now contains 47705 row versions in 147 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO:  "zipcode": moved 0 row versions, truncated 572 to 572 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "public.zipcode"
INFO:  "zipcode": scanned 572 of 572 pages, containing 47705 live rows and 0 dead rows; 3000 rows in sample, 47705 estimated total rows
INFO:  free space map: 108 relations, 128 pages stored; 1760 total pages needed
DETAIL:  Allocated FSM size: 1000 relations + 20000 pages = 182 kB shared memory.
VACUUM
pgsql=#
==end===============================================================================


USING INDEX SCAN

==start===============================================================================
pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1';
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using zipcode_pkey on zipcode  (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.058 rows=1 loops=1)
   Index Cond: (seq = 1)
 Total runtime: 0.152 ms
(3 rows)

pgsql=#


pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1' LIMIT 1;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3.02 rows=1 width=55) (actual time=0.059..0.060 rows=1 loops=1)
   ->  Index Scan using zipcode_pkey on zipcode  (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.054 rows=1 loops=1)
         Index Cond: (seq = 1)
 Total runtime: 0.158 ms
(4 rows)

pgsql=#


WHEN SELECT LAST ROW -----

pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705';
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Index Scan using zipcode_pkey on zipcode  (cost=0.00..3.02 rows=1 width=55) (actual time=0.054..0.059 rows=1 loops=1)
   Index Cond: (seq = 47705)
 Total runtime: 0.150 ms
(3 rows)

pgsql=#


pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705' LIMIT 1;
                                                         QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..3.02 rows=1 width=55) (actual time=0.057..0.057 rows=1 loops=1)
   ->  Index Scan using zipcode_pkey on zipcode  (cost=0.00..3.02 rows=1 width=55) (actual time=0.052..0.052 rows=1 loops=1)
         Index Cond: (seq = 47705)
 Total runtime: 0.156 ms
(4 rows)

pgsql=#
==end===============================================================================



USING SEQUENCE SCAN

==start===============================================================================
pgsql=# set enable_indexscan to off;
SET
pgsql=#

pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1';
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on zipcode  (cost=0.00..1168.31 rows=1 width=55) (actual time=0.032..109.934 rows=1 loops=1)
   Filter: (seq = 1)
 Total runtime: 110.021 ms
(3 rows)

pgsql=#


pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='1' LIMIT 1;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1168.31 rows=1 width=55) (actual time=0.035..0.035 rows=1 loops=1)
   ->  Seq Scan on zipcode  (cost=0.00..1168.31 rows=1 width=55) (actual time=0.030..0.030 rows=1 loops=1)
         Filter: (seq = 1)
 Total runtime: 0.113 ms
(4 rows)

pgsql=#


WHEN SELECT 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=55) (actual time=4.048..110.232 rows=1 loops=1)
   Filter: (seq = 47705)
 Total runtime: 110.322 ms
(3 rows)

pgsql=#


pgsql=# EXPLAIN ANALYZE select * from zipcode where seq='47705' LIMIT 1;
                                                QUERY PLAN
-----------------------------------------------------------------------------------------------------------
 Limit  (cost=0.00..1168.31 rows=1 width=55) (actual time=4.038..4.038 rows=1 loops=1)
   ->  Seq Scan on zipcode  (cost=0.00..1168.31 rows=1 width=55) (actual time=4.033..4.033 rows=1 loops=1)
         Filter: (seq = 47705)
 Total runtime: 4.125 ms
(4 rows)

pgsql=#

==end===============================================================================


I just choose zipcode table for this test.
not only zipcode table but other table also give me same result.

SELECT * FROM table_name WHERE PK = 'xxx'

was always slower than

SELECT * FROM table_name WHERE PK = 'xxx' LIMIT 1

when sequence scan .

i think pgsql tring to find more than 1 row when query executed even if
searching condition is primary key.


ah, why i'm using sequence as PK instead of zip code is
in korea, the small towns doesn't have it's own zipcode
so they share other big city's.
that's why zip code can't be a primary key.
actually, i'm not using sequence to find zipcode.
i made it temporary for this test.

i think there is nobody want to using sequence number to find zipcode,
instead of city name. :-)


Josh Berkus 쓴 글:
Hyun-Sang,

  
before test, I already executed VACUUM FULL.
this result show up after vacuum full.
    

Really?   Your results really look like a bloated table.   Can you run VACUUM 
FULL ANALYZE VERBOSE on the table and post the output?

  
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.
    

Can we see an index scan plan anyway?   EXPLAIN ANALYZE?

Oh, and if this is a zip codes table, why are you using a sequence as the 
primary key instead of just using the zip code? 

  

--------------000201030000080003040402--