Skip site navigation (1) Skip section navigation (2)

Re: "slow" queries

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: Brian Cox <brian(dot)cox(at)ca(dot)com>
Cc: "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: "slow" queries
Date: 2009-03-01 03:15:17
Message-ID: 603c8f070902281915u4d402589wef143ff433a8a3d2@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Sat, Feb 28, 2009 at 9:51 PM, Brian Cox <brian(dot)cox(at)ca(dot)com> wrote:
> Actually, they're all deadlocked. The question is why?
>
> Here's a brief background. The ts_defects table is partitioned by occurrence
> date; each partition contains the rows for 1 day. When the data gets old
> enough, the partition is dropped. Since the correct partition can be
> determined from the occurrence date, there is no trigger: inserts are done
> directly into the correct partition. Multiple threads may be inserting into
> a partition at the same time. The thread that checks for old data to be
> dropped runs at 00:30 each night. It also creates the partition for the next
> day.
>
> Below is the output from:
> select xact_start,query_start,substring(current_query from 0 for 40) from
> pg_stat_activity order by xact_start;

Can you post this again with procpid added to the column list and
without truncating current_query?  And then also post the results of
"select * from pg_locks"?

Is there anything interesting in the postmaster log?

...Robert

In response to

pgsql-performance by date

Next:From: Robert HaasDate: 2009-03-01 03:32:57
Subject: Re: Bad plan for nested loop + limit
Previous:From: Brian CoxDate: 2009-03-01 02:51:32
Subject: "slow" queries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group