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.
[EMAIL PROTECTED]:~$ 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
[EMAIL PROTECTED]:~$ 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
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
