Thread: using limit instead of seeking

Would there be any reasonable performance gain in using LIMIT in an sql
query to print pages of results, instead of seeking to the desired record
and printing a number of records in a while loop?

I have tried both options, but I cannot perceive any significant change in
performance. Is there a way I can actuall test or predict the usefulness
of using one alternative over the other? I am also using SORT in my sql
queries, so perhaps the db server will have to retrieve all records
beforehand anyways in order to perform the sort. In such a case, I guess
using LIMIT wouldn't make much of a difference. On the other hand, there
are perhaps cases when using LIMIT would make a difference.

Any guidance would be appreciated to solving this problem.
Thanks in advance,
Marc

I am trying to optimise a query which looks like:
select prod_base.*, manu_base.name from prod_base, manu_base where
prod_base.mid=manu_base.mid;

manu_base is a table consisting of 3000 manufacturer with an id (not
unique to support synonyms) and a name (declared as varchar(32)).
prod_base is a table of products which each refer to the manufacturer id
(mid).

I have tried creating an index for manu_base using the following commands:
create index manu_mid_idx on "manu_base" using btree ("mid" "int2_ops");
drop index manu_mid_idx
create index manu_mid_idx on "manu_base" using hash ("mid" "int2_ops");
drop index manu_mid_idx

I have then run benchmarks without index, with btree and with hash, but
none seem to be faster than the other. My benchmark program is written in
c and is attached to this email. Here are the results I obtained using
time:

without index:
17.25 real  1.42 user  0.26 sys
with btree:
17.28 real  1.38 user  0.30 sys
with hash:
17.22 real  1.37 user  0.32 sys

If there is any way to make a query quicker when joining a product table
and a manufacturer table, please let me know. I've tried everything and
the results are quite fast enough.

Thanks,
Marc

Without thinking too much about your problem description, I'd just add that I
have found it useful to use the pgsql EXPLAIN mechanism to show where a query
is using sequential scans (i.e., non-indexed lookups).  Generally, you can
then add appropriate indices and verify the seq scan is gone with EXPLAIN.

Cheers.
Ed

admin wrote:

> I am trying to optimise a query which looks like:
> select prod_base.*, manu_base.name from prod_base, manu_base where
> prod_base.mid=manu_base.mid;
>
> manu_base is a table consisting of 3000 manufacturer with an id (not
> unique to support synonyms) and a name (declared as varchar(32)).
> prod_base is a table of products which each refer to the manufacturer id
> (mid).
>
> I have tried creating an index for manu_base using the following commands:
> create index manu_mid_idx on "manu_base" using btree ("mid" "int2_ops");
> drop index manu_mid_idx
> create index manu_mid_idx on "manu_base" using hash ("mid" "int2_ops");
> drop index manu_mid_idx
>
> I have then run benchmarks without index, with btree and with hash, but
> none seem to be faster than the other. My benchmark program is written in
> c and is attached to this email. Here are the results I obtained using
> time:
>
> without index:
> 17.25 real  1.42 user  0.26 sys
> with btree:
> 17.28 real  1.38 user  0.30 sys
> with hash:
> 17.22 real  1.37 user  0.32 sys
>
> If there is any way to make a query quicker when joining a product table
> and a manufacturer table, please let me know. I've tried everything and
> the results are quite fast enough.
>
> Thanks,
> Marc
>
>   ----------------------------------------------------------------------
>                  Name: bench.c
>    bench.c       Type: Plain Text (TEXT/PLAIN)
>              Encoding: BASE64
>           Description: pgsql benchmark

On Tue, 14 Dec 1999, admin wrote:

> [SNIP]
> I have then run benchmarks without index, with btree and with hash, but
> none seem to be faster than the other. My benchmark program is written in
> c and is attached to this email. Here are the results I obtained using
> time:
>
> without index:
> 17.25 real  1.42 user  0.26 sys
> with btree:
> 17.28 real  1.38 user  0.30 sys
> with hash:
> 17.22 real  1.37 user  0.32 sys
>
> If there is any way to make a query quicker when joining a product table
> and a manufacturer table, please let me know. I've tried everything and
> the results are quite fast enough.

did you VACUUM ANALYZE the table after creating the index ?

---
Howie <caffeine@toodarkpark.org>   URL: http://www.toodarkpark.org
"Tell a man that there are 400 billion stars and he'll believe you.
 Tell him a bench has wet paint and he has to touch it."

> I have tried creating an index for manu_base using the following commands:
> create index manu_mid_idx on "manu_base" using btree ("mid" "int2_ops");
> drop index manu_mid_idx
> create index manu_mid_idx on "manu_base" using hash ("mid" "int2_ops");
> drop index manu_mid_idx

You should also try out defining indices on prod_base.

The most efficient way for joining is an "merge join" this requires an
BTREE-index on both fields to be joined.

If EXPLAIN does not show index-usage you should do a VACUUM on both
tables to update statistics.

Elmar