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.