DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:to:subject:cc:in-reply-to:mime-version
	:content-type:content-transfer-encoding:content-disposition
	:references;
	b=Kw/dODcnOu8yAiNTgfauWpTxe1iNZ+e836sW03BE0NdFHpQF2CSI72o/cKo9QSTA0A
	tpxsiudWcMI/tDs+DlbqJx5WXZ/vzF7zo1+rwB+Szo7iNXySSTkZZjI3ob8MYyqd8FuR
	DPv2lEydxw7zBxXB9dNmdPTh2TFZa1ORkqjv8=
Message-ID: <dcc563d10812171057s58200049ydb7b8a2bfd6c3e03@mail.gmail.com>
Date: Wed, 17 Dec 2008 11:57:33 -0700
From: "Scott Marlowe" <scott.marlowe@gmail.com>
To: "Jaime Casanova" <jcasanov@systemguards.com.ec>
Subject: Re: measure database contention
Cc: "Robert Haas" <robertmhaas@gmail.com>,
	"psql performance list" <pgsql-performance@postgresql.org>
In-Reply-To: <3073cc9b0812171019n2244ecfaibf4c514986a69dee@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
References: <3073cc9b0812161132w3ae157a8n69e2b0cccea05ca2@mail.gmail.com>
	<603c8f070812170618g3116a90du94cbf102f0336d59@mail.gmail.com>
	<3073cc9b0812170738v69e43d89r8d6dae87453b4993@mail.gmail.com>
	<603c8f070812170856g7ae06d58q2f487bead5f7be15@mail.gmail.com>
	<3073cc9b0812171019n2244ecfaibf4c514986a69dee@mail.gmail.com>

On Wed, Dec 17, 2008 at 11:19 AM, Jaime Casanova
<jcasanov@systemguards.com.ec> wrote:
> On Wed, Dec 17, 2008 at 11:56 AM, Robert Haas <robertmhaas@gmail.com> wrote:
>>> is the exact query... i think it will be removed later today because
>>> is a bad query anyway... but my fear is that something like happens
>>> even with good ones...
>>>
>>> maybe chekpoints could be the problem?
>>> i have 8.3.5 and condigured checkpoint_timeout in 15 minutes,
>>> chekpoint_segments 6 and checkpoint_completion_target to 0.5
>>
>> Well, it might help if you could provide the query, and the EXPLAIN output.
>>
>
> ok... remember i say it's a bad query ;)
> actually, seems there's a suitable index for that query (i guess it is
> using it because of the order by)
>
> mic=# explain analyze
> mic-# SELECT * FROM tgen_persona ORDER BY empresa_id, persona_id ASC;
>                                                                 QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using pk_tgen_persona on tgen_persona  (cost=0.00..8534.09
> rows=86547 width=884) (actual time=0.096..129.980 rows=86596 loops=1)
>  Total runtime: 175.952 ms
> (2 rows)
>
> as you see, explain analyze says it will execute in 175.952ms and
> because of network transfer of data executing this from pgadmin in
> another machine it runs for 17s... but from time to time pgFouine is
> shown upto 345.11 sec

I know it's a bad query but did you try clustering on that index?
Then a seq scan followed by a sort would likely be cheaper and faster.
 85k rows aren't that many really.