Re: Query speed problems - Mailing list pgsql-performance

On Thu, 17 Apr 2003, Peter Darley wrote:

>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);

    That was indeed significantly faster. *very* significantly
faster.

    As you may guess, I am an SQL newbie, and working my way through
the language. I figured there would be a faster way to do what I was
doing, but sunqueries or joins was the only way I could figure out.

    Again, thanks for the helpful reply, and for your promptness. I
still want to figure out why the subquery version was taking so damned
long, but it's nice to have a working fast solution.

>-----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)
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

--
|  Victor  Danilchenko  | Curiosity was framed;     |
| danilche@cs.umass.edu | Ignorance killed the cat. |
|   CSCF   |   5-4231   |              -- Anonymous |