On Mon, Jan 4, 2010 at 2:41 PM, Brian Cox <brian.cox@xxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
