I have no idea how to simplify this problem, so this is going to be a long question.
For openers, for reasons I won't get into, I normalized out long paragraphs to a table named shared.notes.
Next I have a complicated view with a number of paragraph lookups. Each note_id field is (a) indexed and (b) has a foreign key constraint to the notes table. Pseudo code below:
CREATE VIEW shared.vw_get_the_whole_kit_and_kaboodle AS
SELECT
yada yada
, mi.electrical_note_id
, electrical_notes.note AS electrical_notes
, mi.hvac_note_id
, hvac_notes.note AS hvac_notes
, mi.network_note_id
, network_notes.note AS network_notes
, mi.plumbing_note_id
, plumbing_notes.note AS plumbing_notes
, mi.specification_note_id
, specification_notes.note AS specification_notes
, mi.structural_note_id
, structural_notes.note AS structural_notes
FROM shared.a_table AS mi
JOIN shared.generic_items AS gi
ON mi.generic_item_id = gi.generic_item_id
JOIN shared.manufacturers AS mft
ON mi.manufacturer_id = mft.manufacturer_id
JOIN shared.notes AS electrical_notes
ON mi.electrical_note_id = electrical_notes.note_id
JOIN shared.notes AS hvac_notes
ON mi.hvac_note_id = hvac_notes.note_id
JOIN shared.notes AS plumbing_notes
ON mi.plumbing_note_id = plumbing_notes.note_id
JOIN shared.notes AS specification_notes
ON mi.specification_note_id = specification_notes.note_id
JOIN shared.notes AS structural_notes
ON mi.structural_note_id = structural_notes.note_id
JOIN shared.notes AS network_notes
ON mi.network_note_id = network_notes.note_id
JOIN shared.connectivity AS nc
ON mi.connectivity_id = nc.connectivity_id
WHERE
mi.deletion_date IS NULL;
Then I select against this view:
SELECT
lots of columns...
FROM shared.vw_get_the_whole_kit_and_kaboodle
WHERE
is_active = TRUE
AND is_inventory = FALSE;
Strangely, in the cloud GCP databases, I've not run into problems yet, and there are thousands of rows involved in a number of these tables.
Meanwhile back at the ranch, on my local PC, I've got a test version of the database. SAME EXACT SQL, down to the last letter. Trust me on that. For table definitions, view definitions, indexes... everything.
The cloud will return queries nearly instantaneously.
The local PC will hang--this despite the fact that the PC database has a mere handful of rows each in the various tables. So if one should hang, it ought to be the cloud databases. But it's the other way around; the tiny-dataset database is the one that fails.
Add this plot twist in: if I remove the filter for is_inventory, the query on the PC returns instantaneously. Also, if I just remove, one by one, the joins to the notes table, after about half of them are gone, the PC starts to finish instantaneously. It's almost like it's upset to be hitting the same table so many times with one query.
If I run EXPLAIN (without the ANALYZE option), here's the NO-hang version:
Hash Left Join (cost=31.55..40.09 rows=43 width=751)
Hash Cond: (mi.mounting_location_id = ml.mounting_location_id)
-> Hash Left Join (cost=30.34..38.76 rows=43 width=719)
Hash Cond: (mi.price_type_id = pt.price_type_id)
-> Hash Join (cost=29.25..37.53 rows=43 width=687)
Hash Cond: (mi.connectivity_id = nc.connectivity_id)
-> Nested Loop (cost=28.16..36.21 rows=43 width=655)
Join Filter: (mi.network_note_id = network_notes.note_id)
-> Seq Scan on notes network_notes (cost=0.00..1.01 rows=1 width=48)
-> Nested Loop (cost=28.16..34.66 rows=43 width=623)
Join Filter: (mi.plumbing_note_id = plumbing_notes.note_id)
-> Seq Scan on notes plumbing_notes (cost=0.00..1.01 rows=1 width=48)
-> Hash Join (cost=28.16..33.11 rows=43 width=591)
Hash Cond: (mi.generic_item_id = gi.generic_item_id)
-> Hash Join (cost=5.11..9.95 rows=43 width=559)
Hash Cond: (mi.structural_note_id = structural_notes.note_id)
-> Hash Join (cost=4.09..8.57 rows=43 width=527)
Hash Cond: (mi.specification_note_id = specification_notes.note_id)
-> Hash Join (cost=3.07..7.37 rows=43 width=495)
Hash Cond: (mi.hvac_note_id = hvac_notes.note_id)
-> Hash Join (cost=2.04..5.99 rows=43 width=463)
Hash Cond: (mi.electrical_note_id = electrical_notes.note_id)
-> Hash Join (cost=1.02..4.70 rows=43 width=431)
Hash Cond: (mi.manufacturer_id = mft.manufacturer_id)
-> Seq Scan on mft_items mi (cost=0.00..3.44 rows=43 width=399)
Filter: ((deletion_date IS NULL) AND is_active)
-> Hash (cost=1.01..1.01 rows=1 width=48)
-> Seq Scan on manufacturers mft (cost=0.00..1.01 rows=1 width=48)
-> Hash (cost=1.01..1.01 rows=1 width=48)
-> Seq Scan on notes electrical_notes (cost=0.00..1.01 rows=1 width=48)
-> Hash (cost=1.01..1.01 rows=1 width=48)
-> Seq Scan on notes hvac_notes (cost=0.00..1.01 rows=1 width=48)
-> Hash (cost=1.01..1.01 rows=1 width=48)
-> Seq Scan on notes specification_notes (cost=0.00..1.01 rows=1 width=48)
-> Hash (cost=1.01..1.01 rows=1 width=48)
-> Seq Scan on notes structural_notes (cost=0.00..1.01 rows=1 width=48)
-> Hash (cost=15.80..15.80 rows=580 width=48)
-> Seq Scan on generic_items gi (cost=0.00..15.80 rows=580 width=48)
-> Hash (cost=1.04..1.04 rows=4 width=36)
-> Seq Scan on connectivity nc (cost=0.00..1.04 rows=4 width=36)
-> Hash (cost=1.04..1.04 rows=4 width=36)
-> Seq Scan on price_types pt (cost=0.00..1.04 rows=4 width=36)
-> Hash (cost=1.09..1.09 rows=9 width=48)
-> Seq Scan on mounting_locations ml (cost=0.00..1.09 rows=9 width=48)
And this is the hang version:
Hash Left Join (cost=26.43..38.57 rows=16 width=751)
Hash Cond: (mi.mounting_location_id = ml.mounting_location_id)
-> Hash Left Join (cost=25.23..37.32 rows=16 width=719)
Hash Cond: (mi.price_type_id = pt.price_type_id)
-> Hash Join (cost=24.14..36.18 rows=16 width=687)
Hash Cond: (mi.connectivity_id = nc.connectivity_id)
-> Nested Loop (cost=23.05..35.00 rows=16 width=655)
Join Filter: (mi.network_note_id = network_notes.note_id)
-> Seq Scan on notes network_notes (cost=0.00..1.01 rows=1 width=48)
-> Nested Loop (cost=23.05..33.79 rows=16 width=623)
Join Filter: (mi.structural_note_id = structural_notes.note_id)
-> Seq Scan on notes structural_notes (cost=0.00..1.01 rows=1 width=48)
-> Nested Loop (cost=23.05..32.58 rows=16 width=591)
Join Filter: (mi.electrical_note_id = electrical_notes.note_id)
-> Seq Scan on notes electrical_notes (cost=0.00..1.01 rows=1 width=48)
-> Nested Loop (cost=23.05..31.37 rows=16 width=559)
Join Filter: (mi.specification_note_id = specification_notes.note_id)
-> Seq Scan on notes specification_notes (cost=0.00..1.01 rows=1 width=48)
-> Nested Loop (cost=23.05..30.16 rows=16 width=527)
Join Filter: (mi.plumbing_note_id = plumbing_notes.note_id)
-> Seq Scan on notes plumbing_notes (cost=0.00..1.01 rows=1 width=48)
-> Nested Loop (cost=23.05..28.95 rows=16 width=495)
Join Filter: (mi.hvac_note_id = hvac_notes.note_id)
-> Seq Scan on notes hvac_notes (cost=0.00..1.01 rows=1 width=48)
-> Nested Loop (cost=23.05..27.74 rows=16 width=463)
Join Filter: (mi.manufacturer_id = mft.manufacturer_id)
-> Seq Scan on manufacturers mft (cost=0.00..1.01 rows=1 width=48)
-> Hash Join (cost=23.05..26.53 rows=16 width=431)
Hash Cond: (mi.generic_item_id = gi.generic_item_id)
-> Seq Scan on mft_items mi (cost=0.00..3.44 rows=16 width=399)
Filter: ((deletion_date IS NULL) AND is_active AND (NOT is_inventory))
-> Hash (cost=15.80..15.80 rows=580 width=48)
-> Seq Scan on generic_items gi (cost=0.00..15.80 rows=580 width=48)
-> Hash (cost=1.04..1.04 rows=4 width=36)
-> Seq Scan on connectivity nc (cost=0.00..1.04 rows=4 width=36)
-> Hash (cost=1.04..1.04 rows=4 width=36)
-> Seq Scan on price_types pt (cost=0.00..1.04 rows=4 width=36)
-> Hash (cost=1.09..1.09 rows=9 width=48)
-> Seq Scan on mounting_locations ml (cost=0.00..1.09 rows=9 width=48)
I'd like to understand what I should be doing differently to escape this hang condition. Unfortunately, I'm not clear on what I'm doing wrong.
question from:
https://stackoverflow.com/questions/65893976/how-to-interpret-postgresql-explain-results-when-query-hangs 
