Re: LONG delete with LOTS of FK's

On Thu, May 16, 2013 at 06:01:51PM -0500, Larry Rosenman wrote:
- On 2013-05-16 17:52, David Kerr wrote:
- >On Fri, May 10, 2013 at 11:01:15AM -0500, Larry Rosenman wrote:
- >- On 2013-05-10 10:57, Tom Lane wrote:
- >- >Larry Rosenman <ler(at)lerctr(dot)org> writes:
- >- >On 2013-05-10 09:14, Tom Lane wrote:
- >- >... and verify you get a cheap plan for each referencing table.
- >- >
- >- >We don't :(
- >- >
- >- >Ugh. I bet the problem is that in some of these tables, there are
- >lots
- >- >and lots of duplicate account ids, such that seqscans look like a
- >good
- >- >bet when searching for an otherwise-unknown id. You don't see this
- >- >with a handwritten test for a specific id because then the planner
- >can
- >- >see it's not any of the common values.
- >- >
- >- >9.2 would fix this for you --- any chance of updating?
- >- >
- >- > regards, tom lane
- >- I'll see what we can do. I was looking for a reason, this may be it.
- >-
- >- Thanks for all your help.
- >
- >I haven't seen an explain for this badboy, maybe I missed it (even just
- >a
- >plain explain might be useful) but you may be running into a situation
- >where
- >the planner is trying to materialize or hash 2 big tables.
- >
- >I've actually run into that in the past and had some success in PG9.1
- >running
- >with enable_material=false for some queries.
- >
- >It might be worth a shot to play with that and
- >enable_hashagg/enable_hashjoin=false
- >(If you get a speedup, it points to some tuning/refactoring that could
- >happen)
- >
- >Dave
- I'll take a look tomorrow, but we WERE seeing Seq Scan's against
- multi-million
- row tables, so I suspect Tom is right on with the replanning that's in
- 9.2 fixing
- it, and I'm in the process of validating that.

That seems likely, although you could try enable_seqscan=false as well.

Dave