Wired behavor with LIMIT

From: Thomas Munz <thomas(at)ecommerce(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Wired behavor with LIMIT
Date: 2007-05-25 10:41:02
Message-ID: 4656BD3E.90506@ecommerce.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello List!

I tried today to optmize in our companies internal Application the
querys. I come to a point where I tried, if querys with LIMIT are slower
then querys without limit

I tried following query in 8.2.4. Keep in mind that the table hs_company
only contains 10 rows.

thomas(at)localhost:~$ psql testdb testsuer
Welcome to psql 8.2.4, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

ghcp=# explain analyze select * from hs_company; explain analyze select
* from hs_company limit 10;
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on hs_company (cost=0.00..1.10 rows=10 width=186) (actual
time=0.012..0.034 rows=10 loops=1)
Total runtime: 0.102 ms
(2 rows)

QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.10 rows=10 width=186) (actual time=0.012..0.063
rows=10 loops=1)
-> Seq Scan on hs_company (cost=0.00..1.10 rows=10 width=186)
(actual time=0.007..0.025 rows=10 loops=1)
Total runtime: 0.138 ms
(3 rows)

I runned this query about 100 times and always resulted, that this query
without limit is about 40 ms faster

Now I putted the same query in the file 'sql.sql' and runned it 100
times with:
psql test testuser -f sql.sql
with following results

thomas(at)localhost:~$ psql testdb testuser -f sql.sql
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Seq Scan on hs_company (cost=0.00..1.10 rows=10 width=186) (actual
time=0.013..0.034 rows=10 loops=1)
Total runtime: 0.200 ms
(2 rows)

QUERY PLAN
--------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1.10 rows=10 width=186) (actual time=0.016..0.069
rows=10 loops=1)
-> Seq Scan on hs_company (cost=0.00..1.10 rows=10 width=186)
(actual time=0.008..0.025 rows=10 loops=1)
Total runtime: 0.153 ms
(3 rows)

The querys are equal but has different speeds. Can me someone explain
why that is?

Thomas

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Allison 2007-05-25 11:15:21 Re: bytea & perl
Previous Message Tom Allison 2007-05-25 10:34:52 Re: why postgresql over other RDBMS