Re: Wired behavor with LIMIT

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