From: Scott Marlowe <scott.marlowe@gmail.com>
To: Jaime Casanova <jcasanov@systemguards.com.ec>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: psql performance list <pgsql-performance@postgresql.org>
Subject: Re: measure database contention
Date: Wed, 17 Dec 2008 11:57:33 -0700
Message-ID: <dcc563d10812171057s58200049ydb7b8a2bfd6c3e03@mail.gmail.com> (raw)
In-Reply-To: <3073cc9b0812171019n2244ecfaibf4c514986a69dee@mail.gmail.com>
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.
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: scott.marlowe@gmail.com, jcasanov@systemguards.com.ec, robertmhaas@gmail.com
Subject: Re: measure database contention
In-Reply-To: <dcc563d10812171057s58200049ydb7b8a2bfd6c3e03@mail.gmail.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