Re: Query speed problems

On Thu, 17 Apr 2003, Victor Danilchenko wrote:

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

What does explain analyze show for the query?

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

>     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

Hmm, it doesn't look to me like model.maker=<value> type queries are
indexable with this set of things.  An index on model(maker) might help.