Re: How to debug a connection that's "active" but hanging?

Jurrie Overgoor <postgresql-mailinglist(at)jurr(dot)org> writes:
> Then I tried this. The query plans are indeed not consistent.

> Most of the time the first line of the query plan is: Unique 
> (cost=4892.35..4892.35 rows=1 width=64) [1]

> I have seen other costs: 5818.30, 6350.85 and 6514.73. They all complete
> correctly. [2], [3], [4]

> The plan that leaves the query hanging in the 'active' state starts
> with: Unique  (cost=241.81..241.82 rows=1 width=64) [5]

> That's clearly much lower than the rest. So I suspect the planner making
> a 'wrong' guess there, causing a bad plan, and a long time to execute.

Yeah, evidently.

> Now, where to go from here?

The most likely bet here is that you're populating a table and then
running a query on it before autovacuum has had a chance to catch up
with what you did. Then the planner is working with obsolete stats
or none at all, and it guesses wrong about what to do. The standard
fix is to issue a manual ANALYZE on the table between the data-load
and querying steps of your application.

> Is this considered a bug in PostgreSQL, or
> am I misusing the database engine by doing DROP DATABASE and CREATE
> DATABASE over and over again?

It's not a bug. I suppose in a perfect world the stats would
automatically be up to date all the time, but in the real world
it seems like the cost of that would be exorbitant.

regards, tom lane