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: query looping? |
Date: | 2010-01-04 21:53:18 |
Message-ID: | 603c8f071001041353u52e03014yad4f809befe5b66f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Mon, Jan 4, 2010 at 2:41 PM, Brian Cox <brian(dot)cox(at)ca(dot)com> wrote:
> The query shown below [select count(distinct...] seems to be looping
> (99-101% CPU as shown by top for 11+ hours). This using postgres 8.3.5 on a
> dual quad core machine (Intel(R) Xeon(R) CPU X5460 @ 3.16GHz) with 32G RAM.
> Can I provide any other info to help investigate this issue? Or any thoughts
> on how to prevent/avoid it?
You posted an EXPLAIN ANALYZE showing almost the same query taking 17
seconds, but a crucial difference is that the EXPLAIN ANALYZE shows
the query with the parameters actually in the query, whereas the query
that's actually running for a long time uses bound parameters.
PostgreSQL won't take the particular values into account in planning
that version, which can sometimes lead to a markedly inferior plan.
What do you get if you do this?
PREPARE foo AS <the query, with the $x entries still in there>
EXPLAIN EXECUTE foo(<the values>);
...Robert
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Crawford | 2010-01-04 21:53:29 | Re: DB is slow until DB is reloaded |
Previous Message | Keresztury Balázs | 2010-01-04 21:47:37 | forced sequential scan when condition has current_user |