Re: Duplicate WHERE condition changes performance and plan - Mailing list pgsql-performance

> I don't *think* we are using SSDs  but I'll need to confirm that though.

Confirmed we are not using SSDs but '10K RPM SAS in RAID-10.'

I've also been hunt for other queries that show this behaviour too,
and I've found one. The PG settings/versions will be different in this
example due to the earlier example being for our internal CI/CD tool
which is hosted a on local instance of PG. This example is directly
from our production servers.

Query C (slow):-
SELECT COUNT(1)
FROM proposal.proposal
INNER JOIN proposal.note ON proposal.note.proposal_reference =
proposal.proposal.reference
WHERE 1 = 1
AND proposal.proposal.system_id = 11
AND proposal.proposal.legacy_organisation_id IN (6, 7, 11, 16, 18, 44,
200, 218, 233, 237, 259, 47)
AND proposal.proposal.has_been_anonymised = false
AND proposal.note.legacy_read_by IS NULL
AND proposal.note.type_id IN (1, 4, 9)
AND proposal.note.entry_time > '2020-04-01'
AND proposal.note.entry_time < '2020-05-01';

Query D (fast):-
SELECT COUNT(1)
FROM proposal.proposal
INNER JOIN proposal.note ON proposal.note.proposal_reference =
proposal.proposal.reference
WHERE 1 = 1
AND proposal.proposal.system_id = 11
AND proposal.proposal.legacy_organisation_id IN (6, 7, 11, 16, 18, 44,
200, 218, 233, 237, 259, 47)
AND proposal.proposal.has_been_anonymised = false
AND proposal.proposal.has_been_anonymised = false
AND proposal.note.legacy_read_by IS NULL
AND proposal.note.type_id IN (1, 4, 9)
AND proposal.note.entry_time > '2020-04-01'
AND proposal.note.entry_time < '2020-05-01';

The EXPLAIN ANALYZE for both queries can be found here:-
Query C: https://explain.depesz.com/s/5Mbu
Query D: https://explain.depesz.com/s/jVnH

The table definitions (including the indexes) can be found here:-
proposal.proposal:
https://gist.github.com/indy-singh/6ccd86ff859e7cdad2ec1bf73a61445c
proposal.note: https://gist.github.com/indy-singh/6c1f85ad15cb92e138447a91d8cf3ecb

Data stats:-
proposal.proposal has 10,324,779 rows and once the table specific
conditions are applied there are 39,223 rows left.
proposal.note has 28,97,698 rows and once the table specific
conditions are applied there are 54,359 rows left.

PG version:
PostgreSQL 9.5.17 on x86_64-pc-linux-gnu (Debian 9.5.17-1.pgdg90+1),
compiled by gcc (Debian 6.3.0-18+deb9u1) 6.3.0 20170516, 64-bit

-- SELECT relname, relpages, reltuples, relallvisible, relkind,
relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class
WHERE relname='TABLE_NAME'
Table metadata:-
proposal.proposal:
https://gist.github.com/indy-singh/24e7ec8f3d4e2c3ac73f724cea52f9de
proposal.note: https://gist.github.com/indy-singh/104d6ec7ef8179461eb4f91c121615e0

Index Stats:-
proposal.proposal:
https://gist.github.com/indy-singh/1d41d15addb543bcdafc8641b9d7f036
proposal.note: https://gist.github.com/indy-singh/7a698dec98dd8ef2808345d1802e6b6a

Last Vacuum:-
proposal.proposal: Never
proposal.note: 2020-04-17 15:10:57.256013+01

Last Analyze:
proposal.proposal: Never
proposal.note: 2020-04-07 11:48:49.689622+01

Server Configuration:
https://gist.github.com/indy-singh/b19134873f266ee6ce2b9815504d130c

Indy