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

Re: Query speed problems

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Victor Danilchenko <danilche(at)cs(dot)umass(dot)edu>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Query speed problems
Date: 2003-04-17 21:08:51
Message-ID: 20030417140458.Q92220-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, 17 Apr 2003, Victor Danilchenko wrote:

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

Hmm, well, for this version, it looks like most of the time is probably
going into the sort.  I wonder if raising sort_mem would help this version
of the query (try a set sort_mem=8192; before running the query).  This
isn't likely to get the time below like 160 msec though.

> 	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.
Yeah, it looks like it's already using an index, but I didn't see that
index in the list of indexes on the table in the original mail, wierd.


In response to

pgsql-performance by date

Next:From: Nikolaus DilgerDate: 2003-04-18 01:26:12
Subject: Re: Query speed problems
Previous:From: Victor DanilchenkoDate: 2003-04-17 20:29:57
Subject: Re: Query speed problems

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