Re: Wired behavor with LIMIT

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