Re: Query speed problems - Mailing list pgsql-performance

Victor,
    I'm not sure, but I think an exists might be faster for you.  It wouldn't
have to deal with the Cartesian product of the tables.

SELECT DISTINCT maker.* FROM maker WHERE exists (SELECT 1 FROM model WHERE
model.maker=maker.id);

Thanks,
Peter Darley

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org]On Behalf Of Victor
Danilchenko
Sent: Thursday, April 17, 2003 12:17 PM
To: pgsql-performance@postgresql.org
Subject: [PERFORM] Query speed problems


    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.             |


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)