Re: Wired behavor with LIMIT

[Richard Huxton <dev(at)archonet(dot)com>]

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.

--
  Richard Huxton
  Archonet Ltd