Wired behavor with LIMIT - Mailing list 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@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@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