Re: Query speed problems - Mailing list pgsql-performance

On Thu, 17 Apr 2003, Stephan Szabo wrote:

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

# explain analyze SELECT DISTINCT * FROM maker WHERE id=model.maker;
NOTICE:  Adding missing FROM-clause entry for table "model"
                                                                QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=230.58..255.24 rows=123 width=171) (actual time=238.20..293.21 rows=128 loops=1)
   ->  Sort  (cost=230.58..233.66 rows=1233 width=171) (actual time=238.19..241.07 rows=1233 loops=1)
         Sort Key: maker.id, maker.fullname, maker.contact, maker.phone, maker.service_no, maker.lastuser,
maker.comments
         ->  Merge Join  (cost=0.00..167.28 rows=1233 width=171) (actual time=0.27..81.49 rows=1233 loops=1)
               Merge Cond: ("outer".id = "inner".maker)
               ->  Index Scan using maker_pkey on maker (cost=0.00..52.00 rows=1000 width=164) (actual time=0.11..4.29
rows=137loops=1) 
               ->  Index Scan using makers on model  (cost=0.00..94.28 rows=1233 width=7) (actual time=0.04..27.34
rows=1233loops=1) 
 Total runtime: 295.30 msec
(8 rows)

    Following a suggestion sent in private mail, I have created an
index for model.maker column:

# create index model_maker on model(maker);

    but that doesn't seem to have made an appreciable difference in
performance -- it's only about .05 seconds more than the above number if
I drop the index.

    Many thanks for your help.

>>     "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.
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>

--
|  Victor  Danilchenko  | Curiosity was framed;     |
| danilche@cs.umass.edu | Ignorance killed the cat. |
|   CSCF   |   5-4231   |              -- Anonymous |