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
