Thanks Steve, Answers are inserted below: On 5/18/12 11:09 AM, "Steve Crawford" <scrawford@xxxxxxxxxxxxxxxxxxxx> wrote: >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? It is processing and in fact drives the load up a bit. > >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? The query will run fine many times a night for many nights, then it will hang. Killing that query and running it again results in the same hang indefinitely. The only fix so far to get the query to run again is to drop and rebuild the xrefcampaignmenber.memberid index (I misquoted which index earlier) and then it will once again run fine for many, many days. > >Are there any other queries that may be locking your tables (check >pg_locks)? I did check, and no, no locks. > >Anything of interest in postgresql or system logs? Nope. > >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? No large updates or deletes to any of the tables involved in the query. Statistics seems just fine. > >Does the network connection to the server still exist? Yes. > >Any other weirdness happening on the machine? Nothing at all. > > >> 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; Yeah, the query is poo... autogenerated... the LEFT JOIN is not needed as I have pointed out to the person responsible for the code many times, and the 'in(1)' may indeed have many categories in there. But the OLY one that hangs is the 'in(1)' > >Cheers, >Steve > -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
