On 08/17/2014 07:15 PM, Anastasia Lubennikova wrote:
2014-08-07 0:30 GMT+04:00 Heikki Linnakangas <hlinnakan...@vmware.com>:
* I'm getting two regression failures with this (opr_sanity and join).
opr_sanity failure is corrected.
But there is remain question with join.
I check the latest version of my github repo and there's no fail in join
regression test
All 145 tests passed.
To tell the truth, I don't understand which changes could led to this
failure.
Could you show me regression diffs?
Sure, here you go. It seems like a change in a plan. At a quick glance
it seems harmless: the new plan is identical except that the left and
right side of a join have been reversed. But I don't understand either
why this patch would change that, so it needs to be investigated.
* The regression test queries that use LIMIT are not guaranteed to always
return the same rows, hence they're not very good regression test cases.
I'd suggest using more restricting WHERE clauses, so that each query only
returns a handful of rows.
Thank you for comment, I rewrote wrong queries. But could you explain why
LIMIT queries may return different results? Is it happens because of
different query optimization?
Imagine that you have a table with two rows, A and B. If you run a query
like "SELECT * FROM table LIMIT 1", the system can legally return either
row A or B, because there's no ORDER BY.
Now, admittedly you have a similar problem even without the LIMIT - the
system can legally return the rows in either order - but it's less of an
issue because at least you can quickly see from the diff that the result
set is in fact the same, the rows are just in different order. You could
fix that by adding an ORDER BY to all test queries, but we haven't done
that in the regression suite because then we would not have any test
coverage for cases where you don't have an ORDER BY. As a compromise,
test cases are usually written without an ORDER BY, but if e.g. the
buildfarm starts failing because of differences in the result set order
across platforms, then we add an ORDER BY to make it stable.
* I think it's leaking memory, in GIST scan context. I tested this with a
variant of the regression tests:
insert into gist_tbl select box(point(0.05*i, 0.05*i), point(0.05*i,
0.05*i)),
point(0.05*i, 0.05*i) FROM generate_series(0,
10000000) as i;
CREATE INDEX gist_tbl_point_index ON gist_tbl USING gist (p);
set enable_seqscan=off;
set enable_bitmapscan=off;
explain analyze select p from gist_tbl where p <@ box(point(0,0),
point(9999999,9999999)) and length(p::text) < 10;
while the final query runs, 'top' shows constantly increasing memory usage.
I don't think it's memory leak. After some increasing, memory using remain
the same. It works similar without using indexonlyscan.
No, it's definitely a leak caused by the patch. Test with the attached
patch, which prints out to the server log the amount of memory used by
the GiST scan memory context every 10000 rows. It clearly shows
increasing memory usage all the way to the end of the query.
It's cleaned up at the end of the query, but that's not good enough
because for a large query you might accumulate gigabytes of leaked
memory until the query has finished. If you (manually) apply the same
patch to git master, you'll see that the memory usage stays consistent
and small.
- Heikki
*** /home/heikki/git-sandbox/postgresql/src/test/regress/expected/join.out
2014-08-18 09:38:55.146171394 +0300
--- /home/heikki/git-sandbox/postgresql/src/test/regress/results/join.out
2014-08-18 10:28:30.326491898 +0300
***************
*** 2579,2590 ****
-----------------------------------------------------------
Sort
Sort Key: t1.q1, t1.q2
! -> Hash Left Join
! Hash Cond: (t1.q2 = t2.q1)
Filter: (1 = (SubPlan 1))
! -> Seq Scan on int8_tbl t1
-> Hash
! -> Seq Scan on int8_tbl t2
SubPlan 1
-> Limit
-> Result
--- 2579,2590 ----
-----------------------------------------------------------
Sort
Sort Key: t1.q1, t1.q2
! -> Hash Right Join
! Hash Cond: (t2.q1 = t1.q2)
Filter: (1 = (SubPlan 1))
! -> Seq Scan on int8_tbl t2
-> Hash
! -> Seq Scan on int8_tbl t1
SubPlan 1
-> Limit
-> Result
***************
*** 3589,3603 ****
lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2);
q1 | q2 | q1 | q2
| xq1 | yq1 | yq2
------------------+-------------------+------------------+-------------------+------------------+------------------+-------------------
- 123 | 456 | |
| 123 | |
- 123 | 4567890123456789 | 4567890123456789 | -4567890123456789
| 123 | 4567890123456789 | -4567890123456789
- 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
| 123 | 4567890123456789 | 4567890123456789
- 123 | 4567890123456789 | 4567890123456789 | 123
| 123 | 4567890123456789 | 123
- 4567890123456789 | 123 | 123 | 4567890123456789
| 4567890123456789 | 123 | 4567890123456789
4567890123456789 | 123 | 123 | 456
| 4567890123456789 | 123 | 456
! 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
| 4567890123456789 | 4567890123456789 | -4567890123456789
! 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
| 4567890123456789 | 4567890123456789 | 4567890123456789
4567890123456789 | 4567890123456789 | 4567890123456789 | 123
| 4567890123456789 | 4567890123456789 | 123
4567890123456789 | -4567890123456789 | |
| 4567890123456789 | |
(10 rows)
--- 3589,3603 ----
lateral (values(x.q1,y.q1,y.q2)) v(xq1,yq1,yq2);
q1 | q2 | q1 | q2
| xq1 | yq1 | yq2
------------------+-------------------+------------------+-------------------+------------------+------------------+-------------------
4567890123456789 | 123 | 123 | 456
| 4567890123456789 | 123 | 456
! 4567890123456789 | 123 | 123 | 4567890123456789
| 4567890123456789 | 123 | 4567890123456789
4567890123456789 | 4567890123456789 | 4567890123456789 | 123
| 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123
| 123 | 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
| 4567890123456789 | 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
| 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
| 4567890123456789 | 4567890123456789 | -4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789
| 123 | 4567890123456789 | -4567890123456789
+ 123 | 456 | |
| 123 | |
4567890123456789 | -4567890123456789 | |
| 4567890123456789 | |
(10 rows)
***************
*** 3606,3620 ****
lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
q1 | q2 | q1 | q2
| xq1 | yq1 | yq2
------------------+-------------------+------------------+-------------------+------------------+------------------+-------------------
- 123 | 456 | |
| 123 | |
- 123 | 4567890123456789 | 4567890123456789 | -4567890123456789
| 123 | 4567890123456789 | -4567890123456789
- 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
| 123 | 4567890123456789 | 4567890123456789
- 123 | 4567890123456789 | 4567890123456789 | 123
| 123 | 4567890123456789 | 123
- 4567890123456789 | 123 | 123 | 4567890123456789
| 4567890123456789 | 123 | 4567890123456789
4567890123456789 | 123 | 123 | 456
| 4567890123456789 | 123 | 456
! 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
| 4567890123456789 | 4567890123456789 | -4567890123456789
! 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
| 4567890123456789 | 4567890123456789 | 4567890123456789
4567890123456789 | 4567890123456789 | 4567890123456789 | 123
| 4567890123456789 | 4567890123456789 | 123
4567890123456789 | -4567890123456789 | |
| 4567890123456789 | |
(10 rows)
--- 3606,3620 ----
lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
q1 | q2 | q1 | q2
| xq1 | yq1 | yq2
------------------+-------------------+------------------+-------------------+------------------+------------------+-------------------
4567890123456789 | 123 | 123 | 456
| 4567890123456789 | 123 | 456
! 4567890123456789 | 123 | 123 | 4567890123456789
| 4567890123456789 | 123 | 4567890123456789
4567890123456789 | 4567890123456789 | 4567890123456789 | 123
| 4567890123456789 | 4567890123456789 | 123
+ 123 | 4567890123456789 | 4567890123456789 | 123
| 123 | 4567890123456789 | 123
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | 4567890123456789
| 4567890123456789 | 4567890123456789 | 4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | 4567890123456789
| 123 | 4567890123456789 | 4567890123456789
+ 4567890123456789 | 4567890123456789 | 4567890123456789 | -4567890123456789
| 4567890123456789 | 4567890123456789 | -4567890123456789
+ 123 | 4567890123456789 | 4567890123456789 | -4567890123456789
| 123 | 4567890123456789 | -4567890123456789
+ 123 | 456 | |
| 123 | |
4567890123456789 | -4567890123456789 | |
| 4567890123456789 | |
(10 rows)
***************
*** 3623,3637 ****
lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
q1 | q2
------------------+-------------------
- 123 | 456
- 123 | 4567890123456789
- 123 | 4567890123456789
- 123 | 4567890123456789
4567890123456789 | 123
4567890123456789 | 123
4567890123456789 | 4567890123456789
4567890123456789 | 4567890123456789
4567890123456789 | 4567890123456789
4567890123456789 | -4567890123456789
(10 rows)
--- 3623,3637 ----
lateral (select x.q1,y.q1,y.q2) v(xq1,yq1,yq2);
q1 | q2
------------------+-------------------
4567890123456789 | 123
4567890123456789 | 123
4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
4567890123456789 | 4567890123456789
+ 123 | 4567890123456789
+ 123 | 456
4567890123456789 | -4567890123456789
(10 rows)
***************
*** 3990,4004 ****
Hash Cond: (d.q1 = c.q2)
-> Nested Loop
Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, 42::bigint)),
(COALESCE((COALESCE(b.q2, 42::bigint)), d.q2))
! -> Hash Left Join
Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, 42::bigint))
! Hash Cond: (a.q2 = b.q1)
! -> Seq Scan on public.int8_tbl a
! Output: a.q1, a.q2
-> Hash
! Output: b.q1, (COALESCE(b.q2, 42::bigint))
! -> Seq Scan on public.int8_tbl b
! Output: b.q1, COALESCE(b.q2, 42::bigint)
-> Seq Scan on public.int8_tbl d
Output: d.q1, COALESCE((COALESCE(b.q2, 42::bigint)),
d.q2)
-> Hash
--- 3990,4004 ----
Hash Cond: (d.q1 = c.q2)
-> Nested Loop
Output: a.q1, a.q2, b.q1, d.q1, (COALESCE(b.q2, 42::bigint)),
(COALESCE((COALESCE(b.q2, 42::bigint)), d.q2))
! -> Hash Right Join
Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, 42::bigint))
! Hash Cond: (b.q1 = a.q2)
! -> Seq Scan on public.int8_tbl b
! Output: b.q1, COALESCE(b.q2, 42::bigint)
-> Hash
! Output: a.q1, a.q2
! -> Seq Scan on public.int8_tbl a
! Output: a.q1, a.q2
-> Seq Scan on public.int8_tbl d
Output: d.q1, COALESCE((COALESCE(b.q2, 42::bigint)),
d.q2)
-> Hash
***************
*** 4021,4062 ****
lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
) on c.q2 = ss2.q1,
lateral (select * from int4_tbl i where ss2.y > f1) ss3;
! QUERY PLAN
!
---------------------------------------------------------------------------------------------------------
! Nested Loop
Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, i.f1
! Join Filter: ((COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) > i.f1)
! -> Hash Right Join
! Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1,
(COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2))
! Hash Cond: (d.q1 = c.q2)
! -> Nested Loop
! Output: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2,
(b2.f1)::bigint)), d.q2))
! -> Hash Right Join
! Output: a.q1, a.q2, b.q1, (COALESCE(b.q2,
(b2.f1)::bigint))
! Hash Cond: (b.q1 = a.q2)
! -> Nested Loop
! Output: b.q1, COALESCE(b.q2, (b2.f1)::bigint)
! Join Filter: (b.q1 < b2.f1)
! -> Seq Scan on public.int8_tbl b
! Output: b.q1, b.q2
! -> Materialize
Output: b2.f1
! -> Seq Scan on public.int4_tbl b2
! Output: b2.f1
! -> Hash
Output: a.q1, a.q2
! -> Seq Scan on public.int8_tbl a
! Output: a.q1, a.q2
! -> Seq Scan on public.int8_tbl d
! Output: d.q1, COALESCE((COALESCE(b.q2,
(b2.f1)::bigint)), d.q2)
! -> Hash
! Output: c.q1, c.q2
-> Seq Scan on public.int8_tbl c
Output: c.q1, c.q2
! -> Materialize
! Output: i.f1
! -> Seq Scan on public.int4_tbl i
! Output: i.f1
(34 rows)
-- check processing of postponed quals (bug #9041)
--- 4021,4062 ----
lateral (select q1, coalesce(ss1.x,q2) as y from int8_tbl d) ss2
) on c.q2 = ss2.q1,
lateral (select * from int4_tbl i where ss2.y > f1) ss3;
! QUERY PLAN
!
---------------------------------------------------------------------------------------------
! Hash Right Join
Output: c.q1, c.q2, a.q1, a.q2, b.q1, d.q1, i.f1
! Hash Cond: (d.q1 = c.q2)
! Filter: ((COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)) > i.f1)
! -> Nested Loop
! Output: a.q1, a.q2, b.q1, d.q1, (COALESCE((COALESCE(b.q2,
(b2.f1)::bigint)), d.q2))
! -> Hash Right Join
! Output: a.q1, a.q2, b.q1, (COALESCE(b.q2, (b2.f1)::bigint))
! Hash Cond: (b.q1 = a.q2)
! -> Nested Loop
! Output: b.q1, COALESCE(b.q2, (b2.f1)::bigint)
! Join Filter: (b.q1 < b2.f1)
! -> Seq Scan on public.int8_tbl b
! Output: b.q1, b.q2
! -> Materialize
! Output: b2.f1
! -> Seq Scan on public.int4_tbl b2
Output: b2.f1
! -> Hash
! Output: a.q1, a.q2
! -> Seq Scan on public.int8_tbl a
Output: a.q1, a.q2
! -> Seq Scan on public.int8_tbl d
! Output: d.q1, COALESCE((COALESCE(b.q2, (b2.f1)::bigint)), d.q2)
! -> Hash
! Output: c.q1, c.q2, i.f1
! -> Nested Loop
! Output: c.q1, c.q2, i.f1
-> Seq Scan on public.int8_tbl c
Output: c.q1, c.q2
! -> Materialize
! Output: i.f1
! -> Seq Scan on public.int4_tbl i
! Output: i.f1
(34 rows)
-- check processing of postponed quals (bug #9041)
======================================================================
diff --git a/src/backend/access/gist/gistget.c b/src/backend/access/gist/gistget.c
index b2a20ca..c3a6cfc 100644
--- a/src/backend/access/gist/gistget.c
+++ b/src/backend/access/gist/gistget.c
@@ -562,6 +562,12 @@ gistgettuple(PG_FUNCTION_ARGS)
* It's always head of so->pageData
*/
so->pageData = list_delete_cell(so->pageData, tmpPageData, NULL);
+
+ {
+ static int lastreport = 0;
+ if ((lastreport++) % 10000 == 0)
+ MemoryContextStats(so->giststate->scanCxt);
+ }
PG_RETURN_BOOL(TRUE);
}
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
