Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-performance by date

Next:From: Stephan SzaboDate: 2003-04-17 21:08:51
Subject: Re: Query speed problems
Previous:From: Victor DanilchenkoDate: 2003-04-17 20:24:17
Subject: Re: Query speed problems

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group