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

Postgres query completion status?

From: Richard Neill <rn214(at)cam(dot)ac(dot)uk>
To: pgsql-performance(at)postgresql(dot)org
Subject: Postgres query completion status?
Date: 2009-11-20 05:32:45
Message-ID: 4B0629FD.7050907@cam.ac.uk (view raw or flat)
Thread:
Lists: pgsql-performance
Dear All,

I've just joined this list, so let me first thank you in advance for 
your hospitality.

I'm having lots of trouble with variously slow running queries on a 
production system. I've tried all the "obvious" fixes: changing the 
query planner, checking for indexing, autovacuum, making sure the thing 
has heaps of memory (20GB), running on solid state disks etc.


1. Is there any way to debug or trace a running query? I think I have 
all the logging options turned on, but I'd like to see something like:
  "Currently reading 3452 rows from table_x, at 0.2 us per row" or
whatever, being really, really verbose in the logfiles.

Likewise, is there any way to check whether, for example, postgres is 
running out of work memory?



2. Is there any way, whatsoever, to get any kind of "progress bar" for a 
running query? I know these things have terrible faults, but anything 
monotonic would be better than running completely blind.

[There's a wonderful paper here:
http://pages.cs.wisc.edu/~naughton/includes/papers/multiquery.pdf
which seems to have got 90% of the way there, but appears to have been 
abandoned as it didn't get all 100% of the way]


The operations people in the warehouse are currently going crazy because 
  we can't ever answer the question "when will this query complete?". I 
know it's hard to do accurately, but knowing the difference between "5 
seconds to go" and "5 hours to go" would be so fantastically useful.

Thanks,

Richard



P.S. Sometimes, some queries seem to benefit from being cancelled and 
then immediately re-started. As there are being run in a transaction, I 
can't see how this could make a difference. Am I missing anything 
obvious?  Occasionally, a re-start of postgresql-8.4l itself seems to help.



Responses

pgsql-performance by date

Next:From: Greg WilliamsonDate: 2009-11-20 06:07:08
Subject: Re: Postgres query completion status?
Previous:From: हृषीकेश मेहेंदळ <hashinclude@gmail.com>Date: 2009-11-20 01:22:56
Subject: Partitions and max_locks_per_transaction

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