Re: Query speed problems - Mailing list 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@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)