Tom wrote:
> You would have to show us the actual failure diffs to get much useful
comment, but in general increasing the size of tuple headers could
easily lead to
> changes in plan choices
Thank you Tom. So there is some hope! In effect the query plan is
different for the join and the view tests. The result set is different
only for the 'without_oid' test.
A side question: Are these tests comprehensive, or should I run other
tests just to be sure? Hints on where to find those tests are welcome.
Thanks!
(diff below)
Mayeul
***
~/bin/postgresql_9.3.4/patched_3_64/postgresql-9.3.4/src/test/regress/expected/join.out
2014-03-17 19:35:47.000000000 +0000
---
~/bin/postgresql_9.3.4/patched_3_64/postgresql-9.3.4/src/test/regress/results/join.out
2014-08-20 15:40:56.248603754 +0100
***************
*** 2791,2814 ****
join int4_tbl i1 on b.thousand = f1
right join int4_tbl i2 on i2.f1 = b.tenthous
order by 1;
! QUERY PLAN
!
-----------------------------------------------------------------------------------------
Sort
Sort Key: b.unique1
! -> Nested Loop Left Join
! -> Seq Scan on int4_tbl i2
-> Nested Loop Left Join
Join Filter: (b.unique1 = 42)
-> Nested Loop
-> Nested Loop
-> Seq Scan on int4_tbl i1
! -> Index Scan using tenk1_thous_tenthous
on tenk1 b
! Index Cond: ((thousand = i1.f1) AND
(i2.f1 = tenthous))
-> Index Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = b.unique2)
-> Index Only Scan using tenk1_thous_tenthous on tenk1 c
Index Cond: (thousand = a.thousand)
! (15 rows)
select b.unique1 from
tenk1 a join tenk1 b on a.unique1 = b.unique2
--- 2791,2818 ----
join int4_tbl i1 on b.thousand = f1
right join int4_tbl i2 on i2.f1 = b.tenthous
order by 1;
! QUERY PLAN
!
-------------------------------------------------------------------------------
Sort
Sort Key: b.unique1
! -> Hash Right Join
! Hash Cond: (b.tenthous = i2.f1)
-> Nested Loop Left Join
Join Filter: (b.unique1 = 42)
-> Nested Loop
-> Nested Loop
-> Seq Scan on int4_tbl i1
! -> Bitmap Heap Scan on tenk1 b
! Recheck Cond: (thousand = i1.f1)
! -> Bitmap Index Scan on
tenk1_thous_tenthous
! Index Cond: (thousand = i1.f1)
-> Index Scan using tenk1_unique1 on tenk1 a
Index Cond: (unique1 = b.unique2)
-> Index Only Scan using tenk1_thous_tenthous on tenk1 c
Index Cond: (thousand = a.thousand)
! -> Hash
! -> Seq Scan on int4_tbl i2
! (19 rows)
select b.unique1 from
tenk1 a join tenk1 b on a.unique1 = b.unique2
======================================================================
***
~/bin/postgresql_9.3.4/patched_3_64/postgresql-9.3.4/src/test/regress/expected/select_views_1.out
2014-03-17 19:35:47.000000000 +0000
---
~/bin/postgresql_9.3.4/patched_3_64/postgresql-9.3.4/src/test/regress/results/select_views.out
2014-08-20 15:41:01.212603532 +0100
***************
*** 1413,1423 ****
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
QUERY PLAN
------------------------------------------------------------------------------
! Nested Loop
! Join Filter: (l.cid = r.cid)
-> Seq Scan on credit_usage r
Filter: ((ymd >= '10-01-2011'::date) AND (ymd <
'11-01-2011'::date))
! -> Materialize
-> Subquery Scan on l
Filter: f_leak(l.cnum)
-> Hash Join
--- 1413,1423 ----
WHERE f_leak(cnum) AND ymd >= '2011-10-01' AND ymd < '2011-11-01';
QUERY PLAN
------------------------------------------------------------------------------
! Hash Join
! Hash Cond: (r.cid = l.cid)
-> Seq Scan on credit_usage r
Filter: ((ymd >= '10-01-2011'::date) AND (ymd <
'11-01-2011'::date))
! -> Hash
-> Subquery Scan on l
Filter: f_leak(l.cnum)
-> Hash Join
***************
*** 1446,1456 ****
------------------------------------------------------------------------------------
Subquery Scan on my_credit_card_usage_secure
Filter: f_leak(my_credit_card_usage_secure.cnum)
! -> Nested Loop
! Join Filter: (l.cid = r.cid)
-> Seq Scan on credit_usage r
Filter: ((ymd >= '10-01-2011'::date) AND (ymd <
'11-01-2011'::date))
! -> Materialize
-> Hash Join
Hash Cond: (r_1.cid = l.cid)
-> Seq Scan on credit_card r_1
--- 1446,1456 ----
------------------------------------------------------------------------------------
Subquery Scan on my_credit_card_usage_secure
Filter: f_leak(my_credit_card_usage_secure.cnum)
! -> Hash Join
! Hash Cond: (r.cid = l.cid)
-> Seq Scan on credit_usage r
Filter: ((ymd >= '10-01-2011'::date) AND (ymd <
'11-01-2011'::date))
! -> Hash
-> Hash Join
Hash Cond: (r_1.cid = l.cid)
-> Seq Scan on credit_card r_1
======================================================================
***
~/bin/postgresql_9.3.4/patched_3_64/postgresql-9.3.4/src/test/regress/expected/without_oid.out
2014-03-17 19:35:47.000000000 +0000
---
~/bin/postgresql_9.3.4/patched_3_64/postgresql-9.3.4/src/test/regress/results/without_oid.out
2014-08-20 15:41:02.068603494 +0100
***************
*** 53,59 ****
WHERE relname IN ('wi', 'wo');
?column? | ?column?
----------+----------
! t | 0
(1 row)
DROP TABLE wi;
--- 53,59 ----
WHERE relname IN ('wi', 'wo');
?column? | ?column?
----------+----------
! f | 0
(1 row)
DROP TABLE wi;
======================================================================
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
