Re: Postgres query completion status?

2009/11/20 Richard Neill <rn214(at)cam(dot)ac(dot)uk>

>
>> Greg Williamson wrote:
>>
>>> Richard --
>>>
>>> You might post the results of "EXPLAIN ANALYZE <your SQL here>;" ... be
>>> sure to run it in a transaction if you want to be able roll it back. Perhaps
>>> try "EXPLAIN <your SQL>;" first as it is faster, but EXPLAIN ANALYZE shows
>>> what the planner is doing.
>>>
>>
>
> Here's something very very odd.
> Explain Analyze has now run, in about 4 minutes. (result below)
>
> However, I'd be willing to swear that the last time I ran explain on this
> query about half an hour ago, the final 2 lines were sequential scans.
>
> So, I've just terminated the real job (which uses this select for an
> update) after 77 minutes of fruitless cpu-hogging, and re-started it....
>
> ...This time, the same job ran through in 24 minutes.
> [This is running exactly the same transaction on exactly the same data!]
>
>
> Richard
>
>
>
It looks like your statistics are way out of sync with the real data.

> Nested Loop (cost=885367.03..1123996.87 rows=8686 width=12) (actual
time=248577.879..253168.466 rows=347308 loops=1)

This shows that it thinks there will be 8,686 rows, but actually traverses
347,308.

Have you manually run a VACUUM on these tables? Preferrably a full one if
you can. I notice that you appear ot have multiple sorts going on. Are all
of those actually necessary for your output? Also consider using partial or
multicolumn indexes where useful.

And which version of PostgreSQL are you using?

Thom