Re: Query speed problems

From: "Nikolaus Dilger" <nikolaus(at)dilger(dot)cc>
To: danilche(at)cs(dot)umass(dot)edu
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query speed problems
Date: 2003-04-18 01:26:12
Message-ID: 20030417182613.4395.h015.c001.wm@mail.dilger.cc.criticalpath.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Victor,

What is the issue? You get sub second response time.
Why waste your time trying to make it faster?
If you have a query that runs serveral minutes or hours
then its worthwhile tuning. Or if your query gets
executed several thausend times a day.

Regards,
Nikolaus

On Thu, 17 Apr 2003 15:17:01 -0400 (EDT), Victor
Danilchenko wrote:

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Kevin Brown 2003-04-18 05:11:33 Foreign key performance
Previous Message Stephan Szabo 2003-04-17 21:08:51 Re: Query speed problems