Re: add_path optimization

>>> Tom Lane <tgl@sss.pgh.pa.us> wrote: 
> 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?
Well, of the cases which are selected based on the other criteria,
there would be about one CaseDispo row each.  The main selection
criterion is the Party.searchName, with various security limitations
added.  Where one or more CaseDispo rows exist (it's only included
through a LEFT JOIN), we want only the one with the latest dispoDate. 
Based on the queries which ran under 8.3.5, it's pretty safe to assume
that the number of CaseDispo rows matching the join criteria to Case
would be between 2,300 and 2,400, with only a handful having multiple
matches to the same Case.  There can never be more than one related to
a specific Case for any one dispoDate.
> Is the rowcount for
> the underlying table (17442012 rows in CaseDispo) accurate?
cir=# select count(*) from "CaseDispo"; count
----------17433234
(1 row)