Re: Query speed problems

From: Victor Danilchenko <danilche(at)cs(dot)umass(dot)edu>
To: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Query speed problems
Date: 2003-04-17 20:29:57
Message-ID: Pine.OSX.4.50.0304171624290.567-100000@phobos.cs.umass.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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=137 loops=1)
-> Index Scan using makers on model (cost=0.00..94.28 rows=1233 width=7) (actual time=0.04..27.34 rows=1233 loops=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(at)cs(dot)umass(dot)edu | Ignorance killed the cat. |
| CSCF | 5-4231 | -- Anonymous |

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Stephan Szabo 2003-04-17 21:08:51 Re: Query speed problems
Previous Message Victor Danilchenko 2003-04-17 20:24:17 Re: Query speed problems