Re: [HACKERS] What about LIMIT in SELECT ?

> This is a little bit off-topic,
> I did some timings with latest cvs on my real database
> ( all output redirected to /dev/null ), table contains 8798 records,
> 31 columns, order key have indices.
>
> 1.select count(*) from work_flats;
> 0.02user 0.00system 0:00.18elapsed 10%CPU (0avgtext+0avgdata 0maxresident)k
> 0inputs+0outputs (131major+21minor)pagefaults 0swaps
>
> 2.select * from work_flats order by rooms, metro_id;
> 2.35user 0.25system 0:10.11elapsed 25%CPU (0avgtext+0avgdata 0maxresident)k
> 0inputs+0outputs (131major+2799minor)pagefaults 0swaps
>
> 3.set query_limit to '150';
> SET VARIABLE
> select * from work_flats order by rooms, metro_id;
> 0.06user 0.00system 0:02.75elapsed 2%CPU (0avgtext+0avgdata 0maxresident)k
> 0inputs+0outputs (131major+67minor)pagefaults 0swaps
>
> 4.begin;
> declare tt cursor for
> select * from work_flats order by rooms, metro_id;
> fetch 150 in tt;
> end;
> 0.05user 0.01system 0:02.76elapsed 2%CPU (0avgtext+0avgdata 0maxresident)k
> 0inputs+0outputs (131major+67minor)pagefaults 0swaps
>
> As you can see timings for query_limit and cursor are very similar,
> I didn't expected this. So, in principle, enhanced version of fetch
> (with offset) would cover all we need from LIMIT, but query_limit would be
> still useful, for example to restrict loadness of server.
> Will all enhancements you discussed go to the 6.4 ?
> I'm really interested in testing this stuff because I begin new project
> and everything we discussed here are badly needed.
>

When you say output to /dev/null, is that on the client, on the backend?
I will assume the client, because of the timings you are reporting.

What is the time of this, which has no ORDER BY?

select * from work_flats;

As far as I can tell, the timing differences you are seeing are based on
the fact that the data is not being transfered to the client. This is
the current sole use of query_limit, and a good one. The web-app need
is to prevent processing of the entire table for just a few rows, and
currently query_limit does not do this, though Jan's patches do this.

--
Bruce Momjian | http://www.op.net/~candle
maillist(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026