Re: Query speed problems

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(at)postgresql(dot)org
>[mailto:pgsql-performance-owner(at)postgresql(dot)org]On Behalf Of Victor
>Danilchenko
>Sent: Thursday, April 17, 2003 12:17 PM
>To: pgsql-performance(at)postgresql(dot)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(at)cs(dot)umass(dot)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(at)postgresql(dot)org)
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

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