>>>>>> Table k2c9vst.timeseries: Materialized view "k2c9vst.timeseries" Column | Type | Collation | Nullable | Default --------------------+-----------------------------+-----------+----------+--------- accref | text | | | owner | text | | | embargo | date | | | mime | text | | | accsize | integer | | | ssa_dstitle | text | | | ssa_creatordid | text | | | ssa_pubdid | text | | | ssa_cdate | timestamp without time zone | | | ssa_pdate | timestamp without time zone | | | ssa_bandpass | text | | | ssa_cversion | text | | | ssa_targname | text | | | ssa_targclass | text | | | ssa_redshift | real | | | ssa_targetpos | spoint | | | ssa_snr | real | | | ssa_location | spoint | | | ssa_aperture | double precision | | | ssa_dateobs | double precision | | | ssa_timeext | double precision | | | ssa_specmid | real | | | ssa_specext | real | | | ssa_specstart | real | | | ssa_specend | real | | | ssa_length | bigint | | | ssa_dstype | text | | | ssa_publisher | text | | | ssa_creator | text | | | ssa_collection | text | | | ssa_instrument | text | | | ssa_datasource | text | | | ssa_creationtype | text | | | ssa_reference | text | | | ssa_fluxstaterror | double precision | | | ssa_fluxsyserror | real | | | ssa_fluxcalib | text | | | ssa_binsize | real | | | ssa_spectstaterror | real | | | ssa_spectsyserror | real | | | ssa_speccalib | text | | | ssa_specres | real | | | t_min_mjd | double precision | | | t_max_mjd | double precision | | | t_0 | double precision | | | Indexes: "timeseries_accref" btree (accref) "timeseries_ssa_location" gist (ssa_location) "timeseries_ssa_pubdid" btree (ssa_pubdid) "timeseries_ssa_targname" btree (ssa_targname) "timeseries_t_max_mjd" btree (t_max_mjd) "timeseries_t_min_mjd" btree (t_min_mjd) >>>>>> Table dfbsspec.raw_spectra: Table "dfbsspec.raw_spectra" Column | Type | Collation | Nullable | Default -----------+------------------+-----------+----------+--------- accref | text | | | owner | text | | | embargo | date | | | mime | text | | | accsize | bigint | | | specid | text | | not null | plate | text | | | objectid | text | | | ra | double precision | | | dec | double precision | | | pos | spoint | | | sp_class | text | | | flux | real[] | | | magb | real | | | magr | real | | | snr | real | | | lam_min | real | | | sky | real | | | px_x | real | | | px_y | real | | | pos_ang | real | | | px_length | real | | | pub_did | text | | | Indexes: "raw_spectra_pkey" PRIMARY KEY, btree (specid) "raw_spectra_accref" btree (accref) "raw_spectra_lam_min" btree (lam_min) "raw_spectra_plate" btree (plate) "raw_spectra_pos" gist (pos) "raw_spectra_pub_did" btree (pub_did) "raw_spectra_q3c_raw_spectra" btree (q3c_ang2ipix(ra, "dec")) CLUSTER "raw_spectra_specid" btree (specid) "raw_spectra_spoint_raw_spectra" gist (spoint(radians(ra), radians("dec"))) Rules: cleanupproducts AS ON DELETE TO dfbsspec.raw_spectra DO DELETE FROM dc.products WHERE products.accref = old.accref >>>>>>>> The dfbspsec.ssa view: (the pubdid ends up in ssa_pubdid) CREATE OR REPLACE VIEW dfbsspec.ssa AS SELECT raw_spectra.accref, raw_spectra.owner, raw_spectra.embargo, raw_spectra.mime, raw_spectra.accsize, (raw_spectra.objectid || ' spectrum from '::text) || raw_spectra.plate AS ssa_dstitle, NULL::text AS ssa_creatordid, raw_spectra.pub_did AS ssa_pubdid, NULL::timestamp without time zone AS ssa_cdate, '2018-09-01 00:00:00'::timestamp without time zone AS ssa_pdate, platemeta.emulsion AS ssa_bandpass, '1.0'::text AS ssa_cversion, NULL::text AS ssa_targname, NULL::text AS ssa_targclass, NULL::real AS ssa_redshift, NULL::spoint AS ssa_targetpos, raw_spectra.snr AS ssa_snr, raw_spectra.pos AS ssa_location, 2::double precision / '3600'::numeric::real AS ssa_aperture, platemeta.epoch AS ssa_dateobs, platemeta.exptime AS ssa_timeext, (raw_spectra.lam_min + 0.000000690::double precision) / '2'::numeric::double precision AS ssa_specmid, 0.000000690::double precision - raw_spectra.lam_min AS ssa_specext, raw_spectra.lam_min AS ssa_specstart, 0.000000690 AS ssa_specend, raw_spectra.px_length::integer AS ssa_length, 'spectrum'::text AS ssa_dstype, 'BAO'::text AS ssa_publisher, 'Markarian et al'::text AS ssa_creator, 'DFBS spectra'::text AS ssa_collection, 'Byurakan 1m Schmidt'::text AS ssa_instrument, 'survey'::text AS ssa_datasource, 'archival'::text AS ssa_creationtype, '2007A&A...464.1177M'::text AS ssa_reference, NULL::real AS ssa_fluxstaterror, NULL::real AS ssa_fluxsyserror, 'UNCALIBRATED'::text AS ssa_fluxcalib, 0.0000000050::real AS ssa_binsize, NULL::real AS ssa_spectstaterror, NULL::real AS ssa_spectsyserror, 'ABSOLUTE'::text AS ssa_speccalib, 0.0000000050::real AS ssa_specres, NULL::spoly AS ssa_region, raw_spectra.magb, raw_spectra.magr, raw_spectra.plate, raw_spectra.ra, raw_spectra."dec" FROM dfbsspec.raw_spectra LEFT JOIN dfbsspec.platemeta ON platemeta.plateid = raw_spectra.plate resulting in these types: View "dfbsspec.ssa" Column | Type | Collation | Nullable | Default --------------------+-----------------------------+-----------+----------+--------- accref | text | | | owner | text | | | embargo | date | | | mime | text | | | accsize | bigint | | | ssa_dstitle | text | | | ssa_creatordid | text | | | ssa_pubdid | text | | | ssa_cdate | timestamp without time zone | | | ssa_pdate | timestamp without time zone | | | ssa_bandpass | text | | | ssa_cversion | text | | | ssa_targname | text | | | ssa_targclass | text | | | ssa_redshift | real | | | ssa_targetpos | spoint | | | ssa_snr | real | | | ssa_location | spoint | | | ssa_aperture | double precision | | | ssa_dateobs | double precision | | | ssa_timeext | real | | | ssa_specmid | double precision | | | ssa_specext | double precision | | | ssa_specstart | real | | | ssa_specend | numeric | | | ssa_length | integer | | | ssa_dstype | text | | | ssa_publisher | text | | | ssa_creator | text | | | ssa_collection | text | | | ssa_instrument | text | | | ssa_datasource | text | | | ssa_creationtype | text | | | ssa_reference | text | | | ssa_fluxstaterror | real | | | ssa_fluxsyserror | real | | | ssa_fluxcalib | text | | | ssa_binsize | real | | | ssa_spectstaterror | real | | | ssa_spectsyserror | real | | | ssa_speccalib | text | | | ssa_specres | real | | | ssa_region | spoly | | | magb | real | | | magr | real | | | plate | text | | | ra | double precision | | | dec | double precision | | | >>>>>>>> side show: dfbsspec.platemeta (I think just for completeness) Table "dfbsspec.platemeta" Column | Type | Collation | Nullable | Default ------------+------------------+-----------+----------+--------- plateid | text | | not null | epoch | double precision | | | exptime | real | | | emulsion | text | | | ra_center | real | | | dec_center | real | | | Indexes: "platemeta_pkey" PRIMARY KEY, btree (plateid) "platemeta_t_max_index" btree ((epoch + exptime / '43200'::numeric::double precision)) "platemeta_t_min_index" btree ((epoch - exptime / '43200'::numeric::double precision)) >>>>>>>> Assembled into ivoa.obscore CREATE OR REPLACE VIEW ivoa.obscore AS SELECT ssa.ssa_dstype AS dataproduct_type, NULL::text AS dataproduct_subtype, 2::smallint AS calib_level, ssa.ssa_collection AS obs_collection, ssa.accref AS obs_id, ssa.ssa_dstitle AS obs_title, ssa.ssa_pubdid AS obs_publisher_did, ssa.ssa_creatordid AS obs_creator_did, ssa.accref AS access_url, ssa.mime AS access_format, ssa.accsize / 1024 AS access_estsize, ssa.ssa_targname AS target_name, ssa.ssa_targclass AS target_class, ssa.ra AS s_ra, ssa."dec" AS s_dec, ssa.ssa_aperture::real AS s_fov, NULL::spoly AS s_region, (NULL::numeric / '3600'::numeric)::real AS s_resolution, ssa.ssa_dateobs - 0.2::double precision AS t_min, ssa.ssa_dateobs + 0.2::double precision AS t_max, NULL::double precision AS t_exptime, NULL::double precision AS t_resolution, ssa.ssa_specstart AS em_min, ssa.ssa_specend::real AS em_max, ssa.ssa_specstart / ssa.ssa_specres AS em_res_power, 'phot.flux.density'::text AS o_ucd, NULL::text AS pol_states, 'Byurakan Astrophysical Observatory BAO'::text AS facility_name, ssa.ssa_instrument AS instrument_name, 1::bigint AS s_xel1, 1::bigint AS s_xel2, NULL::bigint AS t_xel, ssa.ssa_length::bigint AS em_xel, NULL::bigint AS pol_xel, NULL::real AS s_pixel_scale, 'em.wl'::text AS em_ucd, ssa.accref || '?preview=True'::text AS preview, 'dfbsspec.ssa'::text AS source_table FROM dfbsspec.ssa UNION ALL SELECT timeseries.ssa_dstype AS dataproduct_type, NULL::text AS dataproduct_subtype, 3::smallint AS calib_level, timeseries.ssa_collection AS obs_collection, timeseries.accref AS obs_id, timeseries.ssa_dstitle AS obs_title, timeseries.ssa_pubdid AS obs_publisher_did, timeseries.ssa_creatordid AS obs_creator_did, timeseries.accref AS access_url, timeseries.mime AS access_format, (timeseries.accsize / 1024)::bigint AS access_estsize, timeseries.ssa_targname AS target_name, timeseries.ssa_targclass AS target_class, degrees(long(timeseries.ssa_location)) AS s_ra, degrees(lat(timeseries.ssa_location)) AS s_dec, timeseries.ssa_aperture::real AS s_fov, NULL::spoly AS s_region, (NULL::numeric / '3600'::numeric)::real AS s_resolution, timeseries.t_min_mjd AS t_min, timeseries.t_max_mjd AS t_max, NULL::double precision AS t_exptime, NULL::double precision AS t_resolution, timeseries.ssa_specstart AS em_min, timeseries.ssa_specend AS em_max, timeseries.ssa_specstart / timeseries.ssa_specres AS em_res_power, 'phot.flux.density;em.wl'::text AS o_ucd, NULL::text AS pol_states, 'ESO'::text AS facility_name, timeseries.ssa_instrument AS instrument_name, 1::bigint AS s_xel1, 1::bigint AS s_xel2, timeseries.ssa_length AS t_xel, 1::bigint AS em_xel, NULL::bigint AS pol_xel, NULL::real AS s_pixel_scale, ' '::text AS em_ucd, timeseries.accref || '?preview=True'::text AS preview, 'k2c9vst.timeseries'::text AS source_table FROM k2c9vst.timeseries >>>>>>>> The other side: The ivoa.obs_radio view (I don't think any of this matters) View "ivoa.obs_radio" Column | Type | Collation | Nullable | Default -------------------------+------+-----------+----------+--------- obs_publisher_did | text | | | s_resolution_min | real | | | s_resolution_max | real | | | s_fov_min | real | | | s_fov_max | real | | | s_maximum_angular_scale | real | | | f_resolution | real | | | t_exp_min | real | | | t_exp_max | real | | | t_exp_mean | real | | | uv_distance_min | real | | | uv_distance_max | real | | | uv_distribution_ecc | real | | | uv_distribution_fill | real | | | instrument_ant_number | real | | | instrument_ant_min_dist | real | | | instrument_ant_max_dist | real | | | instrument_ant_diameter | real | | | instrument_feed | real | | | scan_mode | real | | | tracking_mode | real | | | CREATE OR REPLACE VIEW ivoa.obs_radio AS SELECT main.obs_publisher_did, main.s_resolution_min, main.s_resolution_max, NULL::real AS s_fov_min, NULL::real AS s_fov_max, NULL::real AS s_maximum_angular_scale, NULL::real AS f_resolution, NULL::real AS t_exp_min, NULL::real AS t_exp_max, NULL::real AS t_exp_mean, NULL::real AS uv_distance_min, NULL::real AS uv_distance_max, NULL::real AS uv_distribution_ecc, NULL::real AS uv_distribution_fill, NULL::real AS instrument_ant_number, NULL::real AS instrument_ant_min_dist, NULL::real AS instrument_ant_max_dist, NULL::real AS instrument_ant_diameter, NULL::real AS instrument_feed, NULL::real AS scan_mode, NULL::real AS tracking_mode FROM emi.main >>>>>>>>>> Table emi.main Table "emi.main" Column | Type | Collation | Nullable | Default ---------------------+------------------+-----------+----------+--------- dataproduct_type | text | | | dataproduct_subtype | text | | | calib_level | smallint | | not null | obs_collection | text | | | obs_id | text | | | obs_title | text | | | obs_publisher_did | text | | | obs_creator_did | text | | | access_url | text | | | access_format | text | | | access_estsize | bigint | | | target_name | text | | | target_class | text | | | s_ra | double precision | | | s_dec | double precision | | | s_fov | double precision | | | s_region | spoly | | | s_resolution | double precision | | | t_min | double precision | | | t_max | double precision | | | t_exptime | real | | | t_resolution | real | | | em_min | double precision | | | em_max | double precision | | | em_res_power | double precision | | | o_ucd | text | | | pol_states | text | | | facility_name | text | | | instrument_name | text | | | s_xel1 | bigint | | | s_xel2 | bigint | | | t_xel | bigint | | | em_xel | bigint | | | pol_xel | bigint | | | s_pixel_scale | double precision | | | em_ucd | text | | | source_table | text | | | obsra | real | | | obsdec | real | | | weighting | text | | | s_resolution_min | real | | | s_resolution_max | real | | | Indexes: "main_obs_publisher_did" btree (obs_publisher_did) "main_s_ra_s_dec" btree (s_ra, s_dec) Rules: cleanupproducts AS ON DELETE TO emi.main DO DELETE FROM dc.products WHERE products.accref = old.access_url >>>>>>> The full explain analyze in the "bad" case QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Finalize Aggregate (cost=2808419.71..2808419.72 rows=1 width=8) (actual time=8985.465..8998.717 rows=1 loops=1) -> Gather (cost=2808419.68..2808419.70 rows=4 width=8) (actual time=8985.033..8998.702 rows=5 loops=1) Workers Planned: 4 Workers Launched: 4 -> Partial Aggregate (cost=2808416.68..2808416.69 rows=1 width=8) (actual time=8973.308..8973.316 rows=1 loops=5) -> Hash Join (cost=59.62..2793908.44 rows=5803296 width=0) (actual time=8973.294..8973.302 rows=0 loops=5) Hash Cond: ("*SELECT* 1".obs_publisher_did = main.obs_publisher_did) -> Parallel Append (cost=0.00..2714053.50 rows=5803296 width=58) (actual time=0.097..7840.862 rows=4642680 loops=5) -> Subquery Scan on "*SELECT* 1" (cost=0.00..2685028.32 rows=5803266 width=58) (actual time=0.106..7183.977 rows=4642657 loops=5) -> Parallel Seq Scan on raw_spectra (cost=0.00..2626995.66 rows=5803266 width=756) (actual time=0.105..6471.531 rows=4642657 loops=5) -> Parallel Seq Scan on timeseries (cost=0.00..8.70 rows=70 width=66) (actual time=0.020..0.148 rows=119 loops=1) -> Hash (cost=52.61..52.61 rows=561 width=48) (actual time=1.415..1.417 rows=561 loops=5) Buckets: 1024 Batches: 1 Memory Usage: 52kB -> Seq Scan on main (cost=0.00..52.61 rows=561 width=48) (actual time=0.047..1.048 rows=561 loops=5) Planning Time: 4.520 ms Execution Time: 8998.904 ms (16 rows) >>>>>>>>>>> The query plan when there's only dfbsspec.ssa in the obscore view QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate (cost=4873.00..4873.01 rows=1 width=8) (actual time=2.204..2.205 rows=1 loops=1) -> Nested Loop (cost=0.56..4871.60 rows=561 width=0) (actual time=2.198..2.198 rows=0 loops=1) -> Seq Scan on main (cost=0.00..52.61 rows=561 width=48) (actual time=0.007..0.188 rows=561 loops=1) -> Index Scan using raw_spectra_pub_did on raw_spectra (cost=0.56..8.58 rows=1 width=66) (actual time=0.003..0.003 rows=0 loops=561) Index Cond: (pub_did = main.obs_publisher_did) Planning Time: 0.883 ms Execution Time: 2.273 ms (7 rows)