Re: odd intermittent query hanging issue

On 05/18/2012 09:17 AM, Aaron Burnett wrote:
> Greetings,
>
> I run a handful of queries overnight when traffic is at it's lowest on our
> system. One particular query will run perfectly fine (around 5 seconds)
> for several weeks, then suddenly decide to hang indefinitely and never
> finish. It needs to be killed manually after several hours (I've
> intentionally let it run to see if it would ever finish) in order for it
> to die.
>
> The fix _seems to be_ to drop and rebuild the index on
> xrefchannelmember.member_id. The query then goes back to running in the 5
> seconds and has no problem again for weeks.... until it happens again.
>
> Has anyone heard of such a thing? And if anyone can maybe point me in
> directions to investigate, it would be much appreciated.

Few answers but several questions...

Is the machine busy processing the query or is it idle?

Does it start happening consistently or is it an individual query. I.e.
if you run the query again will it hang or complete? Can you get a query
plan when the query is failing?

Are there any other queries that may be locking your tables (check
pg_locks)?

Anything of interest in postgresql or system logs?

Autovacuum running properly? Are you running any large
updates/deletes/etc just prior to the query such that the  statistics
the planner is using do not reflect reality?

Does the network connection to the server still exist?

Any other weirdness happening on the machine?


> select distinct(id) from member left join xrefchannelmember xrcm on
> id=xrcm.member_id where id not in (Select memberid from
> xrefcampaignmember) and xrcm.channel_id in (1)  order by id asc;
BTW, I have an un-substantiated gut feeling that this query has room for
improvement. I would experiment with substituting "...where not exists
(select..." for the "...not in (select...". You could also try using
"select..... except select memberid from xrefcampaignmember..."

I'm also not sure I understand the left join since you have the
"xcrm.channel_id in (1)" condition so you won't return records in
"member" without a corresponding record in xrefchannelmember anyway.

I also don't understand the "xcrm.channel_id in (1)" instead of
"xcrm.channel_id = 1" unless this is a generated query and there could
be multiple ids in that condition.

Will one of the following (untested) queries return the results you want
and have a better query plan?:

select distinct(id) from member
join xrefchannelmember xrcm on id=xrcm.member_id
where xrcm.channel_id=1
except
select memberid from xrefcampaignmember
order by id asc;

(actually with except the distinct is probably superfluous as well)

or:

select distinct(id) from member
join xrefchannelmember xrcm on id=xrcm.member_id
where xrcm.channel_id=1
and not exists (select 1 from xrefcampaignmember x where x.memberid =
member.id)
order by id asc;

or:

select distinct(id) from member
where
exists (select 1 from xrefchannelmember where member_id = member.id and
channel_id=1)
and not exists (select 1 from xrefcampaignmember where memberid = member.id)
order by id asc;

Cheers,
Steve