Re: add_path optimization

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> If you can attach to the backend with gdb, try "bt", then "cont",
>> then wait a few seconds, then control-C and "bt" again. Repeat
>> five or ten times and see if there's any consistency to the traces.

> Attached.

Hmm, it seems to be spending all its time in this part of the plan:

-> Merge Anti Join (cost=0.00..2069318.25 rows=11628008 width=15)
Merge Cond: (("CD"."countyNo")::smallint = ("CD2"."countyNo")::smallint)
Join Filter: ((("CD2"."dispoDate")::date > ("CD"."dispoDate")::date) AND (("CD2"."caseNo")::text = ("CD"."caseNo")::text))
-> Index Scan using "CaseDispo_pkey" on "CaseDispo" "CD" (cost=0.00..1068306.86 rows=17442012 width=19)
-> Index Scan using "CaseDispo_CountyNoDispoDate" on "CaseDispo" "CD2" (cost=0.00..913801.31 rows=17442012 width=19)

ie

SELECT ... FROM "CaseDispo" "CD"
WHERE (NOT (EXISTS
(
SELECT
1
FROM
"CaseDispo" "CD2"
WHERE (
("CD2"."caseNo" = "CD"."caseNo")
AND ("CD2"."countyNo" = "CD"."countyNo")
AND ("CD2"."dispoDate" > "CD"."dispoDate"))
)))

which in fact the planner thought would be pretty expensive; but not 24
hours worth. I'm not sure at the moment if the cost estimate is just
badly off, or if there's some sort of thinko in the logic. Can you
estimate how many rows would come out of that join? Is the rowcount for
the underlying table (17442012 rows in CaseDispo) accurate?

regards, tom lane