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
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 |