pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Gregory Stark <stark@enterprisedb.com>
To: André Volpato <andre.volpato@ecomtecnologia.com.br>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres not using array
Date: Fri, 22 Aug 2008 11:28:58 +0100
Message-ID: <87myj5jqc5.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <48ADCA78.2090605@ecomtecnologia.com.br>
References: <48AC8292.5000708@ecomtecnologia.com.br>
	<48AD735D.6040403@ecomtecnologia.com.br>
	<23465.1219328880@sss.pgh.pa.us>
	<48AD80FA.30100@ecomtecnologia.com.br>
	<29595.1219345818@sss.pgh.pa.us>
	<48ADCA78.2090605@ecomtecnologia.com.br>

André Volpato <andre.volpato@ecomtecnologia.com.br> writes:

> Tom Lane escreveu:
>>> We are guessing that a dual core 3.0GHz will beat up a quad core 2.2,
>>> at least in this environmnent with less than 4 concurrent queryes.
>>
>> The most you could hope for from that is less than a 50% speedup.  I'd
>> suggest investing some tuning effort first.  Some rethinking of your
>> schema, for example, might buy you orders of magnitude ... with no new
>> hardware investment.
>
> I think we almost reached the tuning limit, without changing the schema.

It's hard to tell from the plan you posted (and with only a brief look) but it
looks to me like your query with that function is basically doing a join but
because the inner side of the join is in your function's index lookup it's
effectively forcing the use of a "nested loop" join. That's usually a good
choice for small queries against big tables but if you're joining a lot of
data there are other join types which are much faster. You might find the
planner can do a better job if you write your query as a plain SQL query and
let the optimizer figure out the best way instead of forcing its hand.


-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning



view thread (12+ messages)  latest in thread

Message-ID: <87myj5jqc5.fsf@oxford.xeocode.com>
Permalink:  ../87myj5jqc5.fsf@oxford.xeocode.com/
Also on:    postgresql.org/message-id/87myj5jqc5.fsf@oxford.xeocode.com

 · 

reply
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: stark@enterprisedb.com, andre.volpato@ecomtecnologia.com.br
  Subject: Re: Postgres not using array
  In-Reply-To: <87myj5jqc5.fsf@oxford.xeocode.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