Well, I did another check on the LIMIT function ( table has without
"where" statment more then 2.000.000 entries) :
select count(*) from hd_conversation where action_int is null;
count
---------
1652888
(1 row)
So, I runned this query now. The query with limit ( which eaven should
select 100.000 entries less then the second one )
is much slower then selecting all entries. This query was also 100 times
executed with allways the same result.
explain ANALYZE select * from hd_conversation where action_int is null
limit 1552888;explain ANALYZE select * from hd_conversation where
action_int is null;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..97491.64 rows=1552888 width=381) (actual
time=6.447..13351.441 rows=1552888 loops=1)
-> Seq Scan on hd_conversation (cost=0.00..103305.78 rows=1645498
width=381) (actual time=6.442..7699.621 rows=1552888 loops=1)
Filter: (action_int IS NULL)
Total runtime: 16185.870 ms
(4 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on hd_conversation (cost=0.00..103305.78 rows=1645498
width=381) (actual time=6.722..10793.863 rows=1652888 loops=1)
Filter: (action_int IS NULL)
Total runtime: 13621.877 ms
(3 rows)
Probably LIMIT creates an 'overhead' that slows down the System for
bigger entries. If I use a smaller amount its faster.
explain ANALYZE select * from hd_conversation where action_int is null
limit 100000;explain ANALYZE select * from hd_conversation where
action_int is null;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..6278.09 rows=100000 width=381) (actual
time=9.715..947.696 rows=100000 loops=1)
-> Seq Scan on hd_conversation (cost=0.00..103305.78 rows=1645498
width=381) (actual time=9.710..535.933 rows=100000 loops=1)
Filter: (action_int IS NULL)
Total runtime: 1154.158 ms
(4 rows)
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Seq Scan on hd_conversation (cost=0.00..103305.78 rows=1645498
width=381) (actual time=0.039..11172.030 rows=1652888 loops=1)
Filter: (action_int IS NULL)
Total runtime: 14071.620 ms
(3 rows)
But should be LIMIT in any case faster in theory?
Richard Huxton wrote:
Thomas Munz wrote:
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.
Probably too small to provide useful measurements.
ghcp=# explain analyze select * from hs_company; explain analyze
select * from hs_company limit 10;
Total runtime: 0.102 ms
Total runtime: 0.138 ms
1. I'm not sure the timings are accurate for sub-millisecond values
2. You've got to parse the LIMIT clause, and then execute it (even if
it does nothing useful)
I runned this query about 100 times and always resulted, that this
query without limit is about 40 ms faster
That's 0.4ms
Now I putted the same query in the file 'sql.sql' and runned it 100
times with:
psql test testuser -f sql.sql
Total runtime: 0.200 ms
Total runtime: 0.153 ms
The querys are equal but has different speeds. Can me someone explain
why that is?
Same as above - you've got to parse & execute the limit clause.
There's no way for the planner to know that the table has exactly 10
rows in it at the time it executes.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
