Re: add_path optimization - Mailing list pgsql-hackers

>>> Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> Yikes!  The impact of the patch is about what I'd expect, but the
fact
>> that planning time has nearly tripled is... way poor.
>
> We're going to need to see the test case, because I don't see that
in
> some simple tests here.

Built the snapshot with this:

BINDIR = /usr/local/pgsql-8.4devel-20090202/bin
DOCDIR = /usr/local/pgsql-8.4devel-20090202/share/doc
HTMLDIR = /usr/local/pgsql-8.4devel-20090202/share/doc
INCLUDEDIR = /usr/local/pgsql-8.4devel-20090202/include
PKGINCLUDEDIR = /usr/local/pgsql-8.4devel-20090202/include
INCLUDEDIR-SERVER = /usr/local/pgsql-8.4devel-20090202/include/server
LIBDIR = /usr/local/pgsql-8.4devel-20090202/lib
PKGLIBDIR = /usr/local/pgsql-8.4devel-20090202/lib
LOCALEDIR = /usr/local/pgsql-8.4devel-20090202/share/locale
MANDIR = /usr/local/pgsql-8.4devel-20090202/share/man
SHAREDIR = /usr/local/pgsql-8.4devel-20090202/share
SYSCONFDIR = /usr/local/pgsql-8.4devel-20090202/etc
PGXS =
/usr/local/pgsql-8.4devel-20090202/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/pgsql-8.4devel-20090202'
'--enable-integer-datetimes' '--enable-debug' '--disable-nls'
'--with-libxml'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE -I/usr/include/libxml2
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing
-fwrapv -g
CFLAGS_SL = -fpic
LDFLAGS = -Wl,--as-needed
-Wl,-rpath,'/usr/local/pgsql-8.4devel-20090202/lib'
LDFLAGS_SL =
LIBS = -lpgport -lxml2 -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.4devel

Ran it with this:

max_connections = 10
shared_buffers = 250MB
max_prepared_transactions = 0
work_mem = 50MB
maintenance_work_mem = 1GB
checkpoint_segments = 20
checkpoint_timeout = 30min
effective_cache_size = 100GB
geqo = off
from_collapse_limit = 20
join_collapse_limit = 20
datestyle = 'iso, mdy'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english'
escape_string_warning = off
sql_inheritance = off
standard_conforming_strings = on

In a new cluster the database was created with this:

create user cirowner;
create user cir;
create user viewer;
create database cir with owner cirowner;
\c cir
revoke create on database cir from public;
revoke create on schema public from public;
grant create on schema public to cirowner;
checkpoint;

Schema dump attached.

The ten tables which are used by the query were loaded through pg_dump
piped to psql.  I then ran VACUUM FREEZE ANALYZE.

I used \timing to capture run times.  Here is the query:

explain
(
SELECT
"C"."caseNo",
"C"."filingDate",
"CY"."countyName",
"S"."descr" AS "statusCodeDescr",
"P"."nameF",
"P"."nameM",
"P"."nameL",
"P"."suffix",
"P"."dob",
"C"."caption",
"CY"."countyNo",
"C"."caseType",
"C"."isSeal",
"C"."isPartySeal",
"LCM"."lastModified",
"P"."searchName",
CASE WHEN (("C"."filingDate" >= DATE '2008-11-01')
AND ("C"."wcisClsCode" = '30709'))
OR (("C"."caseType" IN ('CV','FA'))
AND ("C"."wcisClsCode" = '30711')
AND (EXISTS
(
SELECT
1
FROM
"CaseHist" "CH"
WHERE (
("C"."countyNo" = "CH"."countyNo")
AND ("C"."caseNo" = "CH"."caseNo")
AND ("CH"."eventType" IN ('FWBCA','CCTRO')))
))) THEN TRUE ELSE FALSE END AS "isPetitionerSeal"
FROM
"Case" "C"
JOIN "Party" "P"
ON (("C"."caseNo" = "P"."caseNo")
AND ("C"."countyNo" = "P"."countyNo")
AND TRUE
AND ((("P"."partyType" IN ('JV','CH'))
AND ("C"."caseType" = 'ZZ'))
OR ("P"."partyType" NOT IN ('JV','CH'))))
JOIN "StatusCode" "S"
ON ("C"."statusCode" = "S"."statusCode")
JOIN "County" "CY"
ON ("C"."countyNo" = "CY"."countyNo")
LEFT OUTER JOIN "CaseDispo" "CD"
ON (("CD"."caseNo" = "C"."caseNo")
AND ("CD"."countyNo" = "C"."countyNo")
AND (NOT (EXISTS
(
SELECT
1
FROM
"CaseDispo" "CD2"
WHERE (
("CD2"."caseNo" = "CD"."caseNo")
AND ("CD2"."countyNo" = "CD"."countyNo")
AND ("CD2"."dispoDate" > "CD"."dispoDate"))
))))
LEFT OUTER JOIN "LastCaseMod" "LCM"
ON (("LCM"."caseNo" = "C"."caseNo")
AND ("LCM"."countyNo" = "C"."countyNo")
AND ("LCM"."updateTypeId" = 0))
LEFT OUTER JOIN "WccaPermCaseType" "WPCT"
ON (("C"."caseType" = "WPCT"."caseType")
AND ("C"."countyNo" = "WPCT"."countyNo")
AND ("WPCT"."profileName" = 'PUBLIC'))
WHERE (
(("WPCT"."profileName" IS NOT NULL)
OR (("C"."caseType" IN ('PA','JD'))
AND ("C"."isConfidential" = FALSE)))
AND ("C"."isExpunge" <> TRUE)
AND (NOT (("WPCT"."profileName" = 'PUBLIC')
AND ("C"."caseType" = 'FA')
AND ("C"."wcisClsCode" = '40501')))
AND (NOT (EXISTS
(
SELECT
1
FROM
"HiddenCase" "HCA"
WHERE (
("HCA"."caseNo" = "C"."caseNo")
AND ("HCA"."countyNo" = "C"."countyNo"))
)))
AND ("P"."isSeal" = FALSE)
AND (NOT (("P"."partyType" IN ('PE','PL','JP'))
AND ((("C"."filingDate" >= DATE '2008-11-01')
AND ("C"."wcisClsCode" = '30709'))
OR (("C"."caseType" IN ('CV','FA'))
AND ("C"."wcisClsCode" = '30711')
AND (EXISTS
(
SELECT
1
FROM
"CaseHist" "CHPET"
WHERE (
("C"."countyNo" = "CHPET"."countyNo")
AND ("C"."caseNo" = "CHPET"."caseNo")
AND ("CHPET"."eventType" IN ('FWBCA','CCTRO')))
))))))
AND TRUE
AND ("P"."searchName" LIKE 'HILL,J%')
AND TRUE
AND (NOT (("C"."caseType" IN ('CF','CI','CM','CT','FO','TR'))
AND ("P"."partyType" <> 'DE')))
AND ((("C"."caseType" IN ('JA','JC','JG','JM','JO','JV','JI','TP'))
AND ("P"."partyType" IN ('CH','JV')))
OR (("C"."caseType" NOT IN ('JA','JC','JG','JM','JO','JV','JI','TP'))
AND ("P"."partyType" NOT IN ('CH','JV'))))
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE)
UNION
SELECT
"C"."caseNo",
"C"."filingDate",
"CY"."countyName",
"S"."descr" AS "statusCodeDescr",
"AL"."nameF",
"AL"."nameM",
"AL"."nameL",
"AL"."suffix",
"AL"."dob",
"C"."caption",
"CY"."countyNo",
"C"."caseType",
"C"."isSeal",
"C"."isPartySeal",
"LCM"."lastModified",
"AL"."searchName",
CASE WHEN (("C"."filingDate" >= DATE '2008-11-01')
AND ("C"."wcisClsCode" = '30709'))
OR (("C"."caseType" IN ('CV','FA'))
AND ("C"."wcisClsCode" = '30711')
AND (EXISTS
(
SELECT
1
FROM
"CaseHist" "CH"
WHERE (
("C"."countyNo" = "CH"."countyNo")
AND ("C"."caseNo" = "CH"."caseNo")
AND ("CH"."eventType" IN ('FWBCA','CCTRO')))
))) THEN TRUE ELSE FALSE END AS "isPetitionerSeal"
FROM
"Case" "C"
JOIN "Party" "P"
ON (("C"."caseNo" = "P"."caseNo")
AND ("C"."countyNo" = "P"."countyNo")
AND ((("P"."partyType" IN ('JV','CH'))
AND ("C"."caseType" = 'ZZ'))
OR ("P"."partyType" NOT IN ('JV','CH'))))
JOIN "Alias" "AL"
ON (("P"."caseNo" = "AL"."caseNo")
AND ("P"."countyNo" = "AL"."countyNo")
AND ("P"."partyNo" = "AL"."partyNo")
AND TRUE)
JOIN "StatusCode" "S"
ON ("C"."statusCode" = "S"."statusCode")
JOIN "County" "CY"
ON ("C"."countyNo" = "CY"."countyNo")
LEFT OUTER JOIN "CaseDispo" "CD"
ON (("CD"."caseNo" = "C"."caseNo")
AND ("CD"."countyNo" = "C"."countyNo")
AND (NOT (EXISTS
(
SELECT
1
FROM
"CaseDispo" "CD2"
WHERE (
("CD2"."caseNo" = "CD"."caseNo")
AND ("CD2"."countyNo" = "CD"."countyNo")
AND ("CD2"."dispoDate" > "CD"."dispoDate"))
))))
LEFT OUTER JOIN "LastCaseMod" "LCM"
ON (("LCM"."caseNo" = "C"."caseNo")
AND ("LCM"."countyNo" = "C"."countyNo")
AND ("LCM"."updateTypeId" = 0))
LEFT OUTER JOIN "WccaPermCaseType" "WPCT"
ON (("C"."caseType" = "WPCT"."caseType")
AND ("C"."countyNo" = "WPCT"."countyNo")
AND ("WPCT"."profileName" = 'PUBLIC'))
WHERE (
(("WPCT"."profileName" IS NOT NULL)
OR (("C"."caseType" IN ('PA','JD'))
AND ("C"."isConfidential" = FALSE)))
AND ("C"."isExpunge" <> TRUE)
AND (NOT (("WPCT"."profileName" = 'PUBLIC')
AND ("C"."caseType" = 'FA')
AND ("C"."wcisClsCode" = '40501')))
AND (NOT (EXISTS
(
SELECT
1
FROM
"HiddenCase" "HCA"
WHERE (
("HCA"."caseNo" = "C"."caseNo")
AND ("HCA"."countyNo" = "C"."countyNo"))
)))
AND ("P"."isSeal" = FALSE)
AND (NOT (("P"."partyType" IN ('PE','PL','JP'))
AND ((("C"."filingDate" >= DATE '2008-11-01')
AND ("C"."wcisClsCode" = '30709'))
OR (("C"."caseType" IN ('CV','FA'))
AND ("C"."wcisClsCode" = '30711')
AND (EXISTS
(
SELECT
1
FROM
"CaseHist" "CHPET"
WHERE (
("C"."countyNo" = "CHPET"."countyNo")
AND ("C"."caseNo" = "CHPET"."caseNo")
AND ("CHPET"."eventType" IN ('FWBCA','CCTRO')))
))))))
AND TRUE
AND ("AL"."searchName" LIKE 'HILL,J%')
AND TRUE
AND (NOT (("C"."caseType" IN ('CF','CI','CM','CT','FO','TR'))
AND ("P"."partyType" <> 'DE')))
AND ((("C"."caseType" IN ('JA','JC','JG','JM','JO','JV','JI','TP'))
AND ("P"."partyType" IN ('CH','JV')))
OR (("C"."caseType" NOT IN ('JA','JC','JG','JM','JO','JV','JI','TP'))
AND ("P"."partyType" NOT IN ('CH','JV'))))
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE
AND TRUE))
ORDER BY
"caseNo"
;

Plans from 8.3.5 and 8.4devel attached.

If you need something else, let me know.

-Kevin