From: David Teran <david.teran@cluster9.com>
To: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: speeding up a select with C function?
Date: Sun, 7 Mar 2004 12:42:22 +0100
Message-ID: <7F2B9EA0-702C-11D8-9DA8-000A95C496AC@cluster9.com> (raw)
Hi,
we need to optimize / speed up a simple select:
explain analyze select
((t0.int_value-t1.int_value)*(t0.int_value-t1.int_value))
from job_property t0, job_property t1
where t0.id_job_profile = 5
and t1.id_job_profile = 6
and t1.id_job_attribute = t0.id_job_attribute
and t1.int_value < t0.int_value;
the result from explain analyze is:
first run:
------------------------------------------------------------------------
------------------------------------------------------------------------
------------------------------
Merge Join (cost=8314.36..8336.21 rows=258 width=8) (actual
time=226.544..226.890 rows=43 loops=1)
Merge Cond: ("outer".id_job_attribute = "inner".id_job_attribute)
Join Filter: ("inner".int_value < "outer".int_value)
-> Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual
time=113.781..113.826 rows=232 loops=1)
Sort Key: t0.id_job_attribute
-> Index Scan using job_property__id_job_profile__fk_index on
job_property t0 (cost=0.00..4105.87 rows=1026 width=8) (actual
time=0.045..113.244 rows=232 loops=1)
Index Cond: (id_job_profile = 5)
-> Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual
time=112.504..112.544 rows=254 loops=1)
Sort Key: t1.id_job_attribute
-> Index Scan using job_property__id_job_profile__fk_index on
job_property t1 (cost=0.00..4105.87 rows=1026 width=8) (actual
time=0.067..112.090 rows=254 loops=1)
Index Cond: (id_job_profile = 6)
Total runtime: 227.120 ms
(12 rows)
second run:
------------------------------------------------------------------------
------------------------------------------------------------------------
----------------------------
Merge Join (cost=8314.36..8336.21 rows=258 width=8) (actual
time=4.323..4.686 rows=43 loops=1)
Merge Cond: ("outer".id_job_attribute = "inner".id_job_attribute)
Join Filter: ("inner".int_value < "outer".int_value)
-> Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual
time=2.666..2.700 rows=232 loops=1)
Sort Key: t0.id_job_attribute
-> Index Scan using job_property__id_job_profile__fk_index on
job_property t0 (cost=0.00..4105.87 rows=1026 width=8) (actual
time=0.279..2.354 rows=232 loops=1)
Index Cond: (id_job_profile = 5)
-> Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual
time=1.440..1.477 rows=254 loops=1)
Sort Key: t1.id_job_attribute
-> Index Scan using job_property__id_job_profile__fk_index on
job_property t1 (cost=0.00..4105.87 rows=1026 width=8) (actual
time=0.040..1.133 rows=254 loops=1)
Index Cond: (id_job_profile = 6)
Total runtime: 4.892 ms
(12 rows)
I have run vacuum analyze before executing the statements. I wonder now
if there is any chance to speed this up. Could we use a C function to
access the indexes faster or is there any other chance to speed this
up?
The Server is a dual G5/2GHZ with 8 GB of RAM and a 3.5 TB fiberchannel
RAID. The job_property table is about 1 GB large (checked with dbsize)
and has about 6.800.000 rows.
regards David
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: david.teran@cluster9.com
Subject: Re: speeding up a select with C function?
In-Reply-To: <7F2B9EA0-702C-11D8-9DA8-000A95C496AC@cluster9.com>
References: <7F2B9EA0-702C-11D8-9DA8-000A95C496AC@cluster9.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox