From: Tom Lane <tgl@sss.pgh.pa.us>
To: David Teran <david.teran@cluster9.com>
Cc: Dennis Bjorklund <db@zigo.dhs.org>
Cc: PgSQL Performance ML <pgsql-performance@postgresql.org>
Subject: Re: speeding up a select with C function?
Date: Tue, 09 Mar 2004 10:37:30 -0500
Message-ID: <25391.1078846650@sss.pgh.pa.us> (raw)
In-Reply-To: <AA8E2214-71C1-11D8-A5BE-000A95C496AC@cluster9.com>
References: <Pine.LNX.4.44.0403080720320.13979-100000@zigo.dhs.org>
<AA8E2214-71C1-11D8-A5BE-000A95C496AC@cluster9.com>
David Teran <david.teran@cluster9.com> writes:
> Merge Join (cost=5369.08..5383.14 rows=150 width=4) (actual
> time=2.527..2.874 rows=43 loops=1)
> Merge Cond: ("outer".id_job_attribute = "inner".id_job_attribute)
> Join Filter: ("inner".int_value < "outer".int_value)
> -> Sort (cost=2684.54..2686.37 rows=734 width=6) (actual
> time=1.140..1.177 rows=232 loops=1)
> Sort Key: t0.id_job_attribute
> -> Index Scan using
> job_property_short__id_job_profile__fk_index on job_property_short t0
> (cost=0.00..2649.60 rows=734 width=6) (actual time=0.039..0.820
> rows=232 loops=1)
> Index Cond: (id_job_profile = 5)
> -> Sort (cost=2684.54..2686.37 rows=734 width=6) (actual
> time=1.175..1.223 rows=254 loops=1)
> Sort Key: t1.id_job_attribute
> -> Index Scan using
> job_property_short__id_job_profile__fk_index on job_property_short t1
> (cost=0.00..2649.60 rows=734 width=6) (actual time=0.023..0.878
> rows=254 loops=1)
> Index Cond: (id_job_profile = 6)
> Total runtime: 3.065 ms
> (12 rows)
> So the question is how to tell Postgres to use the index.
Er, which part of that do you think is not using an index?
More generally, it is not necessarily the case that a join *should* use
an index. I'm a bit surprised that the above bothers to sort; I'd
expect a hash join to be more appropriate. Have you tried experimenting
with enable_mergejoin and the other planner-testing settings?
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, david.teran@cluster9.com, db@zigo.dhs.org
Subject: Re: speeding up a select with C function?
In-Reply-To: <25391.1078846650@sss.pgh.pa.us>
* 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