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

Query speed problems

From: Victor Danilchenko <danilche(at)cs(dot)umass(dot)edu>
To: pgsql-performance(at)postgresql(dot)org
Subject: Query speed problems
Date: 2003-04-17 19:17:01
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

	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
SELECT DISTINCT maker.* FROM maker join model ON

	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

	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,

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

|  Victor  Danilchenko  | Any sufficiently advanced       |
| danilche(at)cs(dot)umass(dot)edu | technology is indistinguishable |
|   CSCF   |   5-4231   | from a Perl script.             |


pgsql-performance by date

Next:From: Victor DanilchenkoDate: 2003-04-17 19:38:37
Subject: Re: Query speed problems
Previous:From: dexDate: 2003-04-17 17:48:16
Subject: Performance of Inherits versus Views

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