I was testing a very complex statistical query, with (among other
things) many EXISTS and NOT EXISTS tests against a build of the source
snapshot from 3 September. (The query looks pretty innocent, but
those aren't tables, they're complicated views.) Under 8.3.3 this
query runs successfully, but takes a few hours. I started it last
night before leaving, on the same machine where 8.3.3 has been
running, and in the morning found this:
olr=# explain analyze
SELECT
"MS"."sMatterNo",
CAST(COUNT(*) AS int) AS "count"
FROM
"MatterSearch" "MS"
JOIN "MatterDateStat" "S" ON
(
"S"."matterNo" = "MS"."sMatterNo" AND
"S"."isOnHold" = FALSE
)
WHERE
(
"MS"."matterStatusCode" IN ('OP', 'RO')
)
GROUP BY "MS"."sMatterNo"
;
ERROR: out of memory
DETAIL: Failed on request of size 8.
It was running for about half an hour before I left, and I didn't
notice the error, so I'm pretty sure it took longer than that for this
error to appear.
[EMAIL PROTECTED]:~> df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda2 20G 8.0G 11G 43% /
tmpfs 2.0G 16K 2.0G 1% /dev/shm
/dev/sda3 253G 7.9G 245G 4% /var/pgsql/data
[EMAIL PROTECTED]:~> free -m
total used free shared buffers
cached
Mem: 4049 2239 1809 0 94
1083
-/+ buffers/cache: 1061 2987
Swap: 1027 561 466
There are several development databases on this machine, all fairly
small, but enough that there's usually no significant free memory --
it gets used as cache. The 1.8 GB free this morning suggests that
something allocated and free a lot of memory.
[EMAIL PROTECTED]:~/postgresql-snapshot> uname -a
Linux OLR-DEV-PG 2.6.5-7.286-bigsmp #1 SMP Thu May 31 10:12:58 UTC 2007
i686 i686 i386 GNU/Linux
[EMAIL PROTECTED]:~/postgresql-snapshot> cat /proc/version
Linux version 2.6.5-7.286-bigsmp ([EMAIL PROTECTED]) (gcc version 3.3.3
(SuSE Linux)) #1 SMP Thu May 31 10:12:58 UTC 2007
[EMAIL PROTECTED]:~/postgresql-snapshot> cat /etc/SuSE-release
SUSE LINUX Enterprise Server 9 (i586)
VERSION = 9
PATCHLEVEL = 3
Attached are the plans from 8.3.3 and 8.4devel. Also attached are the
non-default 8.3.3 postgresql.conf settings; the file is the same for
8.4devel except for the port number. I don't know if the specifics of
the views and tables would be useful here, or just noise, so I'll omit
them unless someone asks for them.
What would be the reasonable next step here?
-Kevin
[EMAIL PROTECTED]:~> /usr/local/pgsql-8.4dev/bin/pg_config
BINDIR = /usr/local/pgsql-8.4dev/bin
DOCDIR = /usr/local/pgsql-8.4dev/share/doc
HTMLDIR = /usr/local/pgsql-8.4dev/share/doc
INCLUDEDIR = /usr/local/pgsql-8.4dev/include
PKGINCLUDEDIR = /usr/local/pgsql-8.4dev/include
INCLUDEDIR-SERVER = /usr/local/pgsql-8.4dev/include/server
LIBDIR = /usr/local/pgsql-8.4dev/lib
PKGLIBDIR = /usr/local/pgsql-8.4dev/lib
LOCALEDIR = /usr/local/pgsql-8.4dev/share/locale
MANDIR = /usr/local/pgsql-8.4dev/share/man
SHAREDIR = /usr/local/pgsql-8.4dev/share
SYSCONFDIR = /usr/local/pgsql-8.4dev/etc
PGXS = /usr/local/pgsql-8.4dev/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/usr/local/pgsql-8.4dev'
'--enable-integer-datetimes' '--enable-debug' '--disable-nls'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wendif-labels
-fno-strict-aliasing -g
CFLAGS_SL = -fpic
LDFLAGS = -Wl,-rpath,'/usr/local/pgsql-8.4dev/lib'
LDFLAGS_SL =
LIBS = -lpgport -lz -lreadline -lcrypt -ldl -lm
VERSION = PostgreSQL 8.4devel
[EMAIL PROTECTED]:~> /usr/local/pgsql-8.4dev/bin/pg_controldata
/var/pgsql/data/kgrittn
pg_control version number: 842
Catalog version number: 200808311
Database system identifier: 5242286260647024629
Database cluster state: in production
pg_control last modified: Thu 04 Sep 2008 05:17:28 PM CDT
Latest checkpoint location: 0/26E7A718
Prior checkpoint location: 0/26E7A6D4
Latest checkpoint's REDO location: 0/26E7A718
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/3561
Latest checkpoint's NextOID: 49152
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Time of latest checkpoint: Thu 04 Sep 2008 05:17:28 PM CDT
Minimum recovery ending location: 0/0
Maximum data alignment: 4
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 2000
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by reference
Maximum length of locale name: 128
LC_COLLATE: C
LC_CTYPE: C
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=142884.33..142932.05 rows=3181 width=13)
-> Hash Left Join (cost=25179.44..142868.43 rows=3181 width=13)
Hash Cond: (("*SELECT* 1"."matterNo")::text = ("M"."matterNo")::text)
Join Filter: ((("MH".date)::date <= (('1974-05-15'::date +
generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT
(subplan)))
Filter: ((COALESCE(("MEC"."newStatusCode")::character varying,
'OP'::character varying))::text <> 'CL'::text)
-> Nested Loop (cost=529.05..66375.07 rows=126 width=49)
-> Nested Loop (cost=529.05..66339.68 rows=126 width=81)
Join Filter: ((("*SELECT* 1".date)::date <=
(('1974-05-15'::date + generate_series(0, (('now'::text)::date -
'1974-05-15'::date))))) AND (NOT (subplan)) AND (NOT (subplan)))
-> Result (cost=0.00..0.02 rows=1 width=0)
-> Hash Join (cost=529.05..26811.51 rows=1513 width=83)
Hash Cond: (("*SELECT* 1"."matterNo")::text =
(s."matterNo")::text)
-> Append (cost=6.64..26033.63 rows=64090 width=70)
-> Subquery Scan "*SELECT* 1"
(cost=6.64..25383.01 rows=36954 width=70)
-> Hash Join (cost=6.64..25013.47
rows=36954 width=135)
Hash Cond:
(("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text)
-> Nested Loop
(cost=0.57..23873.98 rows=105156 width=135)
-> Seq Scan on "Matter" "M"
(cost=0.00..379.26 rows=27136 width=112)
Filter:
(("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on
"MatterHist" "MH" (cost=0.57..0.75 rows=8 width=23)
Recheck Cond:
((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text
= ("M"."litigationMatterNo")::text))
-> BitmapOr
(cost=0.57..0.57 rows=8 width=0)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index
Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index
Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Hash (cost=4.37..4.37
rows=136 width=8)
-> Seq Scan on
"MatterEventCode" "MEC" (cost=0.00..4.37 rows=136 width=8)
Filter:
("newStageCode" IS NOT NULL)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..650.62 rows=27136 width=70)
-> Seq Scan on "Matter" "M"
(cost=0.00..379.26 rows=27136 width=112)
Filter: (("matterType")::text <>
'LT'::text)
-> Hash (cost=514.39..514.39 rows=642 width=13)
-> Nested Loop (cost=6.23..514.39 rows=642
width=13)
Join Filter: (((d."matterNo")::text =
(s."litigationMatterNo")::text) OR ((s."litigationMatterNo" IS NULL) AND
((d."matterNo")::text = (s."matterNo")::text)))
-> Bitmap Heap Scan on "Matter" d
(cost=5.68..49.10 rows=642 width=13)
Recheck Cond:
(("matterStatusCode")::text = ANY (('{OP,RO}'::character varying[])::text[]))
-> Bitmap Index Scan on
"Matter_MatterStatusCode" (cost=0.00..5.52 rows=642 width=0)
Index Cond:
(("matterStatusCode")::text = ANY (('{OP,RO}'::character varying[])::text[]))
-> Bitmap Heap Scan on "Matter" s
(cost=0.55..0.68 rows=3 width=26)
Recheck Cond:
(((d."matterNo")::text = (s."litigationMatterNo")::text) OR
((d."matterNo")::text = (s."matterNo")::text))
-> BitmapOr (cost=0.55..0.55
rows=3 width=0)
-> Bitmap Index Scan on
"Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0)
Index Cond:
((d."matterNo")::text = (s."litigationMatterNo")::text)
-> Bitmap Index Scan on
"Matter_pkey" (cost=0.00..0.27 rows=1 width=0)
Index Cond:
((d."matterNo")::text = (s."matterNo")::text)
SubPlan
-> Nested Loop (cost=0.76..24.15 rows=1 width=722)
-> Nested Loop (cost=0.76..23.86 rows=1
width=563)
Join Filter: (NOT (subplan))
-> Index Scan using "Matter_pkey" on
"Matter" (cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text =
($0)::text)
Filter: (("matterType")::text <>
'LT'::text)
-> Bitmap Heap Scan on "MatterHist"
(cost=0.76..1.66 rows=8 width=550)
Recheck Cond:
(((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
OR ((public."MatterHist"."matterNo")::text =
(public."Matter"."litigationMatterNo")::text))
Filter:
((public."MatterHist".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8
width=0)
-> Bitmap Index Scan on
"MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond:
((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
-> Bitmap Index Scan on
"MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond:
((public."MatterHist"."matterNo")::text =
(public."Matter"."litigationMatterNo")::text)
SubPlan
-> Nested Loop (cost=0.76..2.70 rows=1
width=722)
-> Nested Loop (cost=0.76..2.41
rows=1 width=563)
Join Filter:
(ROW((public."MatterHist".date)::date, CASE WHEN
((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
THEN (public."MatterHist"."matterHistSeqNo")::integer ELSE
((public."MatterHist"."matterHistSeqNo")::smallint + 10000) END) >
ROW(($31)::date, CASE WHEN (($32)::text = ($33)::text) THEN ($34)::integer ELSE
(($34)::smallint + 10000) END))
-> Index Scan using
"Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26)
Index Cond:
(("matterNo")::text = ($0)::text)
Filter:
(("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on
"MatterHist" (cost=0.76..1.66 rows=8 width=550)
Recheck Cond:
(((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
OR ((public."MatterHist"."matterNo")::text =
(public."Matter"."litigationMatterNo")::text))
Filter:
((public."MatterHist".date)::date <= $1)
-> BitmapOr
(cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond:
((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond:
((public."MatterHist"."matterNo")::text =
(public."Matter"."litigationMatterNo")::text)
-> Index Scan using
"MatterEventCode_pkey" on "MatterEventCode" "MEC2" (cost=0.00..0.27 rows=1
width=159)
Index Cond:
(("MEC2"."matterEventCode")::text =
(public."MatterHist"."matterEventCode")::text)
Filter:
(("MEC2"."removeMaintCode")::text = 'INA'::text)
-> Index Scan using "MatterEventCode_pkey" on
"MatterEventCode" "MEC1" (cost=0.00..0.27 rows=1 width=159)
Index Cond:
(("MEC1"."matterEventCode")::text =
(public."MatterHist"."matterEventCode")::text)
Filter: (("MEC1"."newMaintCode")::text =
'INA'::text)
-> Result (cost=0.76..3.16 rows=2 width=359)
-> Append (cost=0.76..3.16 rows=2 width=359)
-> Nested Loop (cost=0.76..2.66 rows=1
width=135)
-> Nested Loop (cost=0.76..2.37
rows=1 width=135)
Join Filter:
(ROW(("MH".date)::date, (CASE WHEN (("MH"."matterNo")::text =
("M"."matterNo")::text) THEN ("MH"."matterHistSeqNo")::integer ELSE
(("MH"."matterHistSeqNo")::smallint + 10000) END)::smallint) > ROW(($2)::date,
$3))
-> Index Scan using
"Matter_pkey" on "Matter" "M" (cost=0.00..0.47 rows=1 width=112)
Index Cond:
(("matterNo")::text = ($0)::text)
Filter:
(("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on
"MatterHist" "MH" (cost=0.76..1.66 rows=8 width=23)
Recheck Cond:
((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text
= ("M"."litigationMatterNo")::text))
Filter: (("MH".date)::date
<= $1)
-> BitmapOr
(cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond:
(("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond:
(("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Index Scan using
"MatterEventCode_pkey" on "MatterEventCode" "MEC" (cost=0.00..0.27 rows=1
width=8)
Index Cond:
(("MEC"."matterEventCode")::text = ("MH"."matterEventCode")::text)
Filter: ("MEC"."newStageCode" IS
NOT NULL)
-> Index Scan using "Matter_pkey" on
"Matter" "M" (cost=0.00..0.48 rows=1 width=112)
Index Cond: (("matterNo")::text =
($0)::text)
Filter: ((("matterType")::text <>
'LT'::text) AND (("filedDate")::date <= $1) AND (ROW(("filedDate")::date,
0::smallint) > ROW(($2)::date, $3)))
-> Index Scan using "Matter_pkey" on "Matter" "L"
(cost=0.00..0.27 rows=1 width=13)
Index Cond: (("L"."matterNo")::text = (COALESCE("*SELECT*
1"."litigationMatterNo", "*SELECT* 1"."matterNo"))::text)
-> Hash (cost=24269.98..24269.98 rows=30433 width=70)
-> Nested Loop (cost=7.26..23965.65 rows=30433 width=35)
-> Hash Join (cost=6.74..2200.73 rows=30394 width=22)
Hash Cond: (("MH"."matterEventCode")::text =
("MEC"."matterEventCode")::text)
-> Seq Scan on "MatterHist" "MH"
(cost=0.00..1496.22 rows=105022 width=23)
-> Hash (cost=5.34..5.34 rows=112 width=7)
-> Seq Scan on "MatterEventCode" "MEC"
(cost=0.00..5.34 rows=112 width=7)
Filter: (("newStatusCode" IS NOT NULL)
AND (("newStatusCode")::text <> 'CT'::text))
-> Bitmap Heap Scan on "Matter" "M" (cost=0.52..0.66
rows=3 width=26)
Recheck Cond: ((("MH"."matterNo")::text =
("M"."matterNo")::text) OR (("MH"."matterNo")::text =
("M"."litigationMatterNo")::text))
Filter: (("M"."matterType")::text <> 'LT'::text)
-> BitmapOr (cost=0.52..0.52 rows=3 width=0)
-> Bitmap Index Scan on "Matter_pkey"
(cost=0.00..0.26 rows=1 width=0)
Index Cond: (("MH"."matterNo")::text =
("M"."matterNo")::text)
-> Bitmap Index Scan on
"Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0)
Index Cond: (("MH"."matterNo")::text =
("M"."litigationMatterNo")::text)
SubPlan
-> Nested Loop (cost=0.76..2.66 rows=1 width=35)
-> Nested Loop (cost=0.76..2.37 rows=1 width=36)
Join Filter: (ROW(("MH".date)::date, (CASE WHEN
(("MH"."matterNo")::text = ("M"."matterNo")::text) THEN
("MH"."matterHistSeqNo")::integer ELSE (("MH"."matterHistSeqNo")::smallint +
10000) END)::smallint) > ROW(($25)::date, $26))
-> Index Scan using "Matter_pkey" on "Matter" "M"
(cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text = ($24)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" "MH"
(cost=0.76..1.66 rows=8 width=23)
Recheck Cond: ((("MH"."matterNo")::text =
("M"."matterNo")::text) OR (("MH"."matterNo")::text =
("M"."litigationMatterNo")::text))
Filter: (("MH".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey"
(cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text =
("M"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey"
(cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text =
("M"."litigationMatterNo")::text)
-> Index Scan using "MatterEventCode_pkey" on
"MatterEventCode" "MEC" (cost=0.00..0.27 rows=1 width=7)
Index Cond: (("MEC"."matterEventCode")::text =
("MH"."matterEventCode")::text)
Filter: (("MEC"."newStatusCode" IS NOT NULL) AND
(("MEC"."newStatusCode")::text <> 'CT'::text))
(139 rows)
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
HashAggregate (cost=180410.55..180495.64 rows=5673 width=13)
-> Hash Left Join (cost=52015.63..180382.18 rows=5673 width=13)
Hash Cond: (("*SELECT* 1"."matterNo")::text = ("M"."matterNo")::text)
Join Filter: ((("MH".date)::date <= (('1974-05-15'::date +
generate_series(0, (('now'::text)::date - '1974-05-15'::date))))) AND (NOT
(subplan)))
Filter: ((COALESCE(("MEC"."newStatusCode")::character varying,
'OP'::character varying))::text <> 'CL'::text)
-> Nested Loop (cost=27365.34..63496.22 rows=225 width=49)
-> Hash Anti Join (cost=27365.34..63433.02 rows=225 width=81)
Hash Cond: (("*SELECT* 1"."matterNo")::text = ("*SELECT*
1"."matterNo")::text)
Join Filter: ((("*SELECT* 1".date)::date <=
(('1974-05-15'::date + generate_series(0, (('now'::text)::date -
'1974-05-15'::date))))) AND (ROW(("*SELECT* 1".date)::date, "*SELECT*
1"."matterHistRowOrder") > ROW(("*SELECT* 1".date)::date, "*SELECT*
1"."matterHistRowOrder")))
-> Nested Loop (cost=530.51..34570.69 rows=253 width=87)
Join Filter: ((("*SELECT* 1".date)::date <=
(('1974-05-15'::date + generate_series(0, (('now'::text)::date -
'1974-05-15'::date))))) AND (NOT (subplan)))
-> Result (cost=0.00..0.02 rows=1 width=0)
-> Hash Join (cost=530.51..26813.09 rows=1518
width=83)
Hash Cond: (("*SELECT* 1"."matterNo")::text =
(s."matterNo")::text)
-> Append (cost=6.64..26033.70 rows=64091
width=70)
-> Subquery Scan "*SELECT* 1"
(cost=6.64..25383.06 rows=36955 width=70)
-> Hash Join
(cost=6.64..25013.51 rows=36955 width=135)
Hash Cond:
(("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text)
-> Nested Loop
(cost=0.57..23874.00 rows=105159 width=135)
-> Seq Scan on
"Matter" "M" (cost=0.00..379.28 rows=27136 width=112)
Filter:
(("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan
on "MatterHist" "MH" (cost=0.57..0.75 rows=8 width=23)
Recheck Cond:
((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text
= ("M"."litigationMatterNo")::text))
-> BitmapOr
(cost=0.57..0.57 rows=8 width=0)
-> Bitmap
Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap
Index Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Hash (cost=4.37..4.37
rows=136 width=8)
-> Seq Scan on
"MatterEventCode" "MEC" (cost=0.00..4.37 rows=136 width=8)
Filter:
("newStageCode" IS NOT NULL)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..650.63 rows=27136 width=70)
-> Seq Scan on "Matter" "M"
(cost=0.00..379.28 rows=27136 width=112)
Filter:
(("matterType")::text <> 'LT'::text)
-> Hash (cost=515.82..515.82 rows=644
width=13)
-> Nested Loop (cost=6.24..515.82
rows=644 width=13)
Join Filter:
(((d."matterNo")::text = (s."litigationMatterNo")::text) OR
((s."litigationMatterNo" IS NULL) AND ((d."matterNo")::text =
(s."matterNo")::text)))
-> Bitmap Heap Scan on "Matter" d
(cost=5.69..49.14 rows=644 width=13)
Recheck Cond:
(("matterStatusCode")::text = ANY ('{OP,RO}'::text[]))
-> Bitmap Index Scan on
"Matter_MatterStatusCode" (cost=0.00..5.53 rows=644 width=0)
Index Cond:
(("matterStatusCode")::text = ANY ('{OP,RO}'::text[]))
-> Bitmap Heap Scan on "Matter" s
(cost=0.55..0.68 rows=3 width=26)
Recheck Cond:
(((d."matterNo")::text = (s."litigationMatterNo")::text) OR
((d."matterNo")::text = (s."matterNo")::text))
-> BitmapOr
(cost=0.55..0.55 rows=3 width=0)
-> Bitmap Index Scan
on "Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0)
Index Cond:
((d."matterNo")::text = (s."litigationMatterNo")::text)
-> Bitmap Index Scan
on "Matter_pkey" (cost=0.00..0.27 rows=1 width=0)
Index Cond:
((d."matterNo")::text = (s."matterNo")::text)
SubPlan
-> Nested Loop (cost=1.52..5.10 rows=1 width=0)
-> Nested Loop Anti Join (cost=1.52..4.82
rows=1 width=4)
Join Filter:
(ROW((public."MatterHist".date)::date, CASE WHEN
((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
THEN (public."MatterHist"."matterHistSeqNo")::integer ELSE
((public."MatterHist"."matterHistSeqNo")::smallint + 10000) END) >
ROW((public."MatterHist".date)::date, CASE WHEN
((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
THEN (public."MatterHist"."matterHistSeqNo")::integer ELSE
((public."MatterHist"."matterHistSeqNo")::smallint + 10000) END))
-> Nested Loop (cost=0.76..2.25
rows=1 width=36)
-> Index Scan using
"Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26)
Index Cond:
(("matterNo")::text = ($4)::text)
Filter:
(("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on
"MatterHist" (cost=0.76..1.66 rows=8 width=23)
Recheck Cond:
(((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
OR ((public."MatterHist"."matterNo")::text =
(public."Matter"."litigationMatterNo")::text))
Filter:
((public."MatterHist".date)::date <= $1)
-> BitmapOr
(cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond:
((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index Cond:
((public."MatterHist"."matterNo")::text =
(public."Matter"."litigationMatterNo")::text)
-> Nested Loop (cost=0.76..2.53
rows=1 width=32)
-> Nested Loop
(cost=0.76..2.25 rows=1 width=36)
-> Index Scan using
"Matter_pkey" on "Matter" (cost=0.00..0.47 rows=1 width=26)
Index Cond:
(("matterNo")::text = ($4)::text)
Filter:
(("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on
"MatterHist" (cost=0.76..1.66 rows=8 width=23)
Recheck Cond:
(((public."MatterHist"."matterNo")::text = (public."Matter"."matterNo")::text)
OR ((public."MatterHist"."matterNo")::text =
(public."Matter"."litigationMatterNo")::text))
Filter:
((public."MatterHist".date)::date <= $1)
-> BitmapOr
(cost=0.76..0.76 rows=8 width=0)
-> Bitmap
Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index
Cond: ((public."MatterHist"."matterNo")::text =
(public."Matter"."matterNo")::text)
-> Bitmap
Index Scan on "MatterHist_pkey" (cost=0.00..0.38 rows=4 width=0)
Index
Cond: ((public."MatterHist"."matterNo")::text =
(public."Matter"."litigationMatterNo")::text)
-> Index Scan using
"MatterEventCode_pkey" on "MatterEventCode" "MEC2" (cost=0.00..0.27 rows=1
width=4)
Index Cond:
(("MEC2"."matterEventCode")::text =
(public."MatterHist"."matterEventCode")::text)
Filter:
(("MEC2"."removeMaintCode")::text = 'INA'::text)
-> Index Scan using "MatterEventCode_pkey"
on "MatterEventCode" "MEC1" (cost=0.00..0.27 rows=1 width=4)
Index Cond:
(("MEC1"."matterEventCode")::text =
(public."MatterHist"."matterEventCode")::text)
Filter: (("MEC1"."newMaintCode")::text
= 'INA'::text)
-> Hash (cost=26033.70..26033.70 rows=64091 width=38)
-> Append (cost=6.64..26033.70 rows=64091 width=38)
-> Subquery Scan "*SELECT* 1"
(cost=6.64..25383.06 rows=36955 width=38)
-> Hash Join (cost=6.64..25013.51
rows=36955 width=135)
Hash Cond:
(("MH"."matterEventCode")::text = ("MEC"."matterEventCode")::text)
-> Nested Loop
(cost=0.57..23874.00 rows=105159 width=135)
-> Seq Scan on "Matter" "M"
(cost=0.00..379.28 rows=27136 width=112)
Filter:
(("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on
"MatterHist" "MH" (cost=0.57..0.75 rows=8 width=23)
Recheck Cond:
((("MH"."matterNo")::text = ("M"."matterNo")::text) OR (("MH"."matterNo")::text
= ("M"."litigationMatterNo")::text))
-> BitmapOr
(cost=0.57..0.57 rows=8 width=0)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index
Cond: (("MH"."matterNo")::text = ("M"."matterNo")::text)
-> Bitmap Index
Scan on "MatterHist_pkey" (cost=0.00..0.28 rows=4 width=0)
Index
Cond: (("MH"."matterNo")::text = ("M"."litigationMatterNo")::text)
-> Hash (cost=4.37..4.37
rows=136 width=8)
-> Seq Scan on
"MatterEventCode" "MEC" (cost=0.00..4.37 rows=136 width=8)
Filter:
("newStageCode" IS NOT NULL)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..650.63 rows=27136 width=38)
-> Seq Scan on "Matter" "M"
(cost=0.00..379.28 rows=27136 width=112)
Filter: (("matterType")::text <>
'LT'::text)
-> Index Scan using "Matter_pkey" on "Matter" "L"
(cost=0.00..0.27 rows=1 width=13)
Index Cond: (("L"."matterNo")::text = (COALESCE("*SELECT*
1"."litigationMatterNo", "*SELECT* 1"."matterNo"))::text)
-> Hash (cost=24269.86..24269.86 rows=30434 width=70)
-> Nested Loop (cost=7.26..23965.52 rows=30434 width=35)
-> Hash Join (cost=6.74..2199.88 rows=30395 width=22)
Hash Cond: (("MH"."matterEventCode")::text =
("MEC"."matterEventCode")::text)
-> Seq Scan on "MatterHist" "MH"
(cost=0.00..1495.35 rows=105025 width=23)
-> Hash (cost=5.34..5.34 rows=112 width=7)
-> Seq Scan on "MatterEventCode" "MEC"
(cost=0.00..5.34 rows=112 width=7)
Filter: (("newStatusCode" IS NOT NULL)
AND (("newStatusCode")::text <> 'CT'::text))
-> Bitmap Heap Scan on "Matter" "M" (cost=0.52..0.66
rows=3 width=26)
Recheck Cond: ((("MH"."matterNo")::text =
("M"."matterNo")::text) OR (("MH"."matterNo")::text =
("M"."litigationMatterNo")::text))
Filter: (("M"."matterType")::text <> 'LT'::text)
-> BitmapOr (cost=0.52..0.52 rows=3 width=0)
-> Bitmap Index Scan on "Matter_pkey"
(cost=0.00..0.26 rows=1 width=0)
Index Cond: (("MH"."matterNo")::text =
("M"."matterNo")::text)
-> Bitmap Index Scan on
"Matter_LitigationMatterNo" (cost=0.00..0.27 rows=2 width=0)
Index Cond: (("MH"."matterNo")::text =
("M"."litigationMatterNo")::text)
SubPlan
-> Nested Loop (cost=0.76..2.65 rows=1 width=0)
-> Nested Loop (cost=0.76..2.37 rows=1 width=4)
Join Filter: (ROW(("MH".date)::date, (CASE WHEN
(("MH"."matterNo")::text = ("M"."matterNo")::text) THEN
("MH"."matterHistSeqNo")::integer ELSE (("MH"."matterHistSeqNo")::smallint +
10000) END)::smallint) > ROW(($2)::date, $3))
-> Index Scan using "Matter_pkey" on "Matter" "M"
(cost=0.00..0.47 rows=1 width=26)
Index Cond: (("matterNo")::text = ($0)::text)
Filter: (("matterType")::text <> 'LT'::text)
-> Bitmap Heap Scan on "MatterHist" "MH"
(cost=0.76..1.66 rows=8 width=23)
Recheck Cond: ((("MH"."matterNo")::text =
("M"."matterNo")::text) OR (("MH"."matterNo")::text =
("M"."litigationMatterNo")::text))
Filter: (("MH".date)::date <= $1)
-> BitmapOr (cost=0.76..0.76 rows=8 width=0)
-> Bitmap Index Scan on "MatterHist_pkey"
(cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text =
("M"."matterNo")::text)
-> Bitmap Index Scan on "MatterHist_pkey"
(cost=0.00..0.38 rows=4 width=0)
Index Cond: (("MH"."matterNo")::text =
("M"."litigationMatterNo")::text)
-> Index Scan using "MatterEventCode_pkey" on
"MatterEventCode" "MEC" (cost=0.00..0.27 rows=1 width=4)
Index Cond: (("MEC"."matterEventCode")::text =
("MH"."matterEventCode")::text)
Filter: (("MEC"."newStatusCode" IS NOT NULL) AND
(("MEC"."newStatusCode")::text <> 'CT'::text))
(140 rows)
listen_addresses = '*'
port = 5512
max_connections = 200
shared_buffers = 256MB
temp_buffers = 10MB
max_prepared_transactions = 0
work_mem = 16MB
maintenance_work_mem = 400MB
max_fsm_pages = 1000000
bgwriter_lru_maxpages = 1000
bgwriter_lru_multiplier = 4.0
wal_buffers = 256kB
checkpoint_segments = 50
seq_page_cost = 0.1
random_page_cost = 0.1
effective_cache_size = 3GB
geqo = off
default_statistics_target = 100
from_collapse_limit = 20
join_collapse_limit = 20
logging_collector = on
log_connections = on
log_disconnections = on
log_line_prefix = '[%m] %p %q<%u %d %r> '
autovacuum_naptime = 1min
autovacuum_vacuum_threshold = 10
autovacuum_analyze_threshold = 10
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
