Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group