Query speed problems - Mailing list pgsql-performance

    Hi,

    In the process of developing an API for web/perl/postrgres
interactions, I have come up against a peculiar problem; a rather simple
query, run on two relatively small tables, takes as much as 0.4 seconds
on my development system (it's a P2 266, which in this case is a good
thing, as it exposes speed issues). I tried accomplishging the same
thing via subqueries and joins, and both methods give me similarly bad
result (join query is a little slower, but only a little).

    The queries I have tested are as follows:

SELECT DISTINCT maker.* FROM maker,model WHERE maker.id=model.maker
SELECT DISTINCT maker.* FROM maker join model ON maker.id=model.maker

    The point of the queries is to extract only the maker rows which
are referenced from the model table. I would happily use another way to
achieve the same end, should anyone suggest it.

    "maker" has only 137 rows, "model" only 1233 rows. I test the
performance in perl, by taking time right before and after query
execution. Executing the queries takes anywhere between .3 and .5
seconds, depending on some other factors (removing the 'distinct'
keyword from the 1st query shaves about .1 second off of the execution
time for example).

    These execution times seem ridiculous. Any idea what the culprit
may be? I hope it's not the text fields, 'cuz those fields are
important.

    Both tables are quite simple:

# \d maker
              Table "public.maker"
   Column   |         Type          | Modifiers
------------+-----------------------+-----------
 id         | character varying(4)  | not null
 fullname   | character varying(20) |
 contact    | character varying(20) |
 phone      | character varying(15) |
 service_no | character varying(20) |
 lastuser   | character varying(30) |
 comments   | text                  |
Indexes: maker_pkey primary key btree (id)
Triggers: RI_ConstraintTrigger_18881,
          RI_ConstraintTrigger_18882

# \d model
                                Table "public.model"
    Column     |         Type          |                  Modifiers
---------------+-----------------------+---------------------------------------------
 id            | integer               | not null default nextval('model_ids'::text)
 name          | character varying(20) | not null
 maker         | character varying(4)  |
 type_hardware | character varying(4)  |
 fullname      | character varying(40) |
 spec          | character varying(50) |
 lastuser      | character varying(30) |
 comments      | text                  |
 size_cap      | character varying(10) |
Indexes: model_pkey primary key btree (id),
         unique_model unique btree (name, maker, type_hardware)
Check constraints: "nonempty_fullname" (fullname > ''::character varying)
Foreign Key constraints: valid_maker FOREIGN KEY (maker) REFERENCES \
                           maker(id) ON UPDATE NO ACTION ON DELETE NO ACTION,
                         valid_type FOREIGN KEY (type_hardware)
REFERENCES type_hardware(id) ON UPDATE NO ACTION ON DELETE NO ACTION

--
|  Victor  Danilchenko  | Any sufficiently advanced       |
| danilche@cs.umass.edu | technology is indistinguishable |
|   CSCF   |   5-4231   | from a Perl script.             |