Re: BUG #15577: Query returns different results when executed multiple times

I've modified the script:
1. I Added SET force_parallel_mode = on;:

*SET force_parallel_mode = on;*
explain (costs off, analyze)
SELECT ta.id AS table_a_id,
tc.id as table_c_id,
tba.id AS table_b_id

2. I've changed group and group_type to be enums (we have them as enums in
db):
drop table if exists table_c;
drop table if exists table_d;
*drop type if exists "group";*
*drop type if exists "group_type";*

*create type group_type AS ENUM ('A', 'B');*
*create type "group" AS ENUM ('A', 'B');*

Updated version of the script is in attachment.

When I execute it, I get:

c:\Program Files\PostgreSQL\11\bin>psql -U postgres -f
"C:\mockup_bug15577.sql" test
DROP VIEW
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TYPE
DROP TYPE
CREATE TABLE
INSERT 0 20000
CREATE TYPE
CREATE TYPE
CREATE TABLE
INSERT 0 200000
CREATE TABLE
INSERT 0 101736
CREATE TABLE
INSERT 0 8
INSERT 0 55
CREATE INDEX
CREATE INDEX
CREATE VIEW
ANALYZE
SET
SET
ALTER TABLE
DROP VIEW
DROP TABLE
DROP TABLE
DROP TABLE
DROP TABLE
DROP TYPE
DROP TYPE
CREATE TYPE
CREATE TYPE
CREATE TABLE
INSERT 0 20000
CREATE TABLE
INSERT 0 200000
CREATE TABLE
INSERT 0 101736
CREATE TABLE
INSERT 0 8
INSERT 0 55
CREATE INDEX
CREATE INDEX
CREATE VIEW
ANALYZE
SET

QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (actual time=1.185..81.170 rows=112 loops=1)
-> Gather (actual time=1.179..95.573 rows=112 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Nested Loop (actual time=0.076..7.182 rows=56 loops=2)
-> Hash Join (actual time=0.050..4.592 rows=936 loops=2)
Hash Cond: (tc.table_d_id = td.id)
-> Parallel Seq Scan on table_c tc (actual
time=0.004..4.358 rows=1755 loops=2)
Filter: (table_e_id = 4)
Rows Removed by Filter: 49114
-> Hash (actual time=0.019..0.020 rows=8 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on table_d td (actual
time=0.011..0.014 rows=8 loops=1)
Filter: ("group" = 'A'::"group")
Rows Removed by Filter: 55
-> Index Scan using table_b_idx_target_id on table_b
(actual time=0.003..0.003 rows=0 loops=1871)
Index Cond: (target_id = tc.id)
Filter: ((date >= '2018-08-10'::date) AND (date <=
'2018-09-01'::date) AND (((target_id = tc.id) AND (group_type =
'A'::group_type)) OR (source_id = tc.id)))
Rows Removed by Filter: 1
-> Index Scan using table_a_uq_001 on table_a ta (actual
time=0.003..0.003 rows=0 loops=112)
Index Cond: ((table_c_id = tc.id) AND (date = '2018-08-31'::date))
Planning Time: 0.773 ms
Execution Time: 96.037 ms
(23 rows)

Nr of rows doesn't change from execution to execution.

On Mon, Jan 7, 2019 at 1:20 PM Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
wrote:

> On Mon, Jan 7, 2019 at 10:29 PM Bartosz Polnik <bartoszpolnik(at)gmail(dot)com>
> wrote:
> >
> > > Can you confirm if there are concurrent changes being made to table_b
> > > perhaps while the query is running?
> >
> > I confirm. There are no concurrent changes being made and I'm the only
> client connecting to that db.
> >
> > > Do you still get the variation with an otherwise idle database with no
> > > open transactions that's just received a complete VACUUM?
> >
> > I executed VACUUM (FULL, ANALYZE) on all tables from the query, but I'm
> still getting different results.
> >
> > With your script, I'm always getting the same output, so it must be
> something else.
>
> What do you get if you run it with SET force_parallel_mode = on?
>
> --
> Thomas Munro
> http://www.enterprisedb.com
>