Jeff Nichols Jeff Nichols - 1 month ago 8
SQL Question

Explain Analyze - Postgres - Increase Speed on View

I've got a lengthy view that is built using WITH conditions. I found that the WITH condition was the best (only) way that I can get the desired results needed for the application. Below is my query for the view.

WITH lv_materials AS (
SELECT part_base_parts.id,
materials.id AS materials_id,
materials.code,
materials.full_name,
materials.description,
materials.status_id,
materials.is_web,
materials.web_name,
materials.rank
FROM ((part_base_parts
JOIN parts_to_materials ON ((part_base_parts.id = parts_to_materials.part_base_part_id)))
JOIN materials ON ((parts_to_materials.material_id = materials.id)))
), lv_partbaseparts AS (
SELECT part_base_parts.id AS part_base_parts_id,
part_base_parts.code,
part_base_parts.part_type_id,
part_base_parts.is_web
FROM part_base_parts
), lv_female_threads AS (
SELECT part_base_parts.id,
threads.id AS female_threads_id,
threads.code,
threads.thread_size,
((threads.description)::text || (unit_of_measurement_suffixes.description)::text) AS female_threads_description,
threads.clearance_hole_id,
threads.tolerance_id,
threads.unit_of_measurement_id,
threads.thread_series_id,
threads.status_id,
threads.is_web,
threads.web_name,
threads.rank,
threads.major_dia,
threads.minor_dia,
threads.pitch,
threads.depth,
threads.male_length,
threads.min_body_length,
threads.min_full_depth,
threads.threads_per_uom,
threads.screw_id
FROM (((part_base_parts
JOIN parts_to_threads_female ON ((part_base_parts.id = parts_to_threads_female.part_base_part_id)))
JOIN threads ON ((parts_to_threads_female.thread_id = threads.id)))
JOIN unit_of_measurement_suffixes ON ((threads.unit_of_measurement_id = unit_of_measurement_suffixes.unit_of_measurement_id)))
WHERE (unit_of_measurement_suffixes.type = (3)::numeric)
), lv_male_threads AS (
SELECT part_base_parts.id,
threads.id AS male_thread_id,
threads.code,
threads.thread_size,
((threads.description)::text || (unit_of_measurement_suffixes.description)::text) AS female_thread_description,
threads.clearance_hole_id,
threads.tolerance_id,
threads.unit_of_measurement_id,
threads.thread_series_id,
threads.status_id,
threads.is_web,
threads.web_name,
threads.rank,
threads.major_dia,
threads.minor_dia,
threads.pitch,
threads.depth,
threads.male_length,
threads.min_body_length,
threads.min_full_depth,
threads.threads_per_uom,
threads.screw_id
FROM (((part_base_parts
JOIN parts_to_threads_male ON ((part_base_parts.id = parts_to_threads_male.part_base_part_id)))
JOIN threads ON ((parts_to_threads_male.thread_id = threads.id)))
JOIN unit_of_measurement_suffixes ON ((threads.unit_of_measurement_id = unit_of_measurement_suffixes.unit_of_measurement_id)))
WHERE (unit_of_measurement_suffixes.type = (3)::numeric)
), lv_lengths AS (
SELECT part_base_parts.id,
measurements.id AS lengths_id,
measurements.fractional,
measurements."decimal",
measurements.unit_of_measurement_id,
measurements.millimeters,
measurements.is_web,
measurements.rank,
measurements.description,
measurements.web_name
FROM ((part_base_parts
JOIN parts_to_measurements ON ((part_base_parts.id = parts_to_measurements.part_base_part_id)))
JOIN measurements ON ((parts_to_measurements.measurement_id = measurements.id)))
), lv_ods AS (
SELECT part_base_parts.id,
measurements.id AS ods_id,
measurements.fractional,
measurements."decimal",
measurements.unit_of_measurement_id,
measurements.millimeters,
measurements.is_web,
measurements.rank,
measurements.description,
measurements.web_name
FROM ((part_base_parts
JOIN parts_to_ods ON ((part_base_parts.id = parts_to_ods.part_base_part_id)))
JOIN measurements ON ((parts_to_ods.measurement_id = measurements.id)))
), lv_profiles AS (
SELECT part_base_parts.id,
profiles.id AS profiles_id,
profiles.code,
profiles.description,
profiles.is_web,
profiles.web_name,
profiles.rank
FROM ((part_base_parts
JOIN parts_to_profiles ON ((part_base_parts.id = parts_to_profiles.part_base_part_id)))
JOIN profiles ON ((parts_to_profiles.profile_id = profiles.id)))
), lv_unit_of_measurements AS (
SELECT part_base_parts.id,
unit_of_measurements.id AS unit_of_measurements_id,
unit_of_measurements.code,
unit_of_measurements.description,
unit_of_measurements.is_web,
unit_of_measurements.web_name,
unit_of_measurements.rank
FROM ((part_base_parts
JOIN parts_to_unit_of_measurements ON ((part_base_parts.id = parts_to_unit_of_measurements.part_base_part_id)))
JOIN unit_of_measurements ON ((parts_to_unit_of_measurements.unit_of_measurement_id = unit_of_measurements.id)))
)
SELECT lv_materials.materials_id,
lv_materials.code AS materials_code,
lv_materials.full_name AS materials_full_name,
lv_materials.description AS materials_description,
lv_materials.status_id AS materials_status_id,
lv_materials.is_web AS materials_is_web,
lv_materials.web_name AS materials_web_name,
lv_materials.rank AS materials_rank,
lv_partbaseparts.part_base_parts_id,
lv_partbaseparts.code AS part_base_parts_code,
lv_partbaseparts.part_type_id AS part_base_parts_part_type_id,
lv_partbaseparts.is_web AS part_base_parts_is_web,
lv_lengths.lengths_id,
lv_lengths.fractional AS lengths_fractional,
lv_lengths."decimal" AS lengths_decimal,
lv_lengths.unit_of_measurement_id AS lengths_unit_of_measurement_id,
lv_lengths.millimeters AS lengths_millimeters,
lv_lengths.is_web AS lengths_is_web,
lv_lengths.rank AS lengths_rank,
lv_lengths.description AS lengths_description,
lv_lengths.web_name AS lengths_web_name,
lv_ods.ods_id,
lv_ods.fractional AS ods_fractional,
lv_ods."decimal" AS ods_decimal,
lv_ods.unit_of_measurement_id AS ods_unit_of_measurement_id,
lv_ods.millimeters AS ods_millimeters,
lv_ods.is_web AS ods_is_web,
lv_ods.rank AS od_rank,
lv_ods.description AS ods_description,
lv_ods.web_name AS ods_web_name,
lv_profiles.profiles_id,
lv_profiles.code AS profiles_code,
lv_profiles.description AS profiles_description,
lv_profiles.is_web AS profiles_is_web,
lv_profiles.web_name AS profiles_web_name,
lv_profiles.rank AS profiles_rank,
lv_unit_of_measurements.unit_of_measurements_id,
lv_unit_of_measurements.code AS unit_of_measurements_code,
lv_unit_of_measurements.description AS unit_of_measurements_description,
lv_unit_of_measurements.is_web AS unit_of_measurements_is_web,
lv_unit_of_measurements.web_name AS unit_of_measurements_web_name,
lv_unit_of_measurements.rank AS unit_of_measurements_rank,
lv_female_threads.female_threads_id,
lv_female_threads.code AS female_threads_code,
lv_female_threads.thread_size AS female_threads_thread_size,
lv_female_threads.female_threads_description,
lv_female_threads.clearance_hole_id AS female_threads_clearance_hole_id,
lv_female_threads.tolerance_id AS female_threads_tolerance_id,
lv_female_threads.unit_of_measurement_id AS female_threads_unit_of_measurement_id,
lv_female_threads.thread_series_id AS female_threads_thread_series_id,
lv_female_threads.status_id AS female_threads_status_id,
lv_female_threads.is_web AS female_threads_is_web,
lv_female_threads.web_name AS female_threads_web_name,
lv_female_threads.rank AS female_threads_rank,
lv_female_threads.major_dia AS female_threads_major_dia,
lv_female_threads.minor_dia AS female_threads_minor_dia,
lv_female_threads.pitch AS female_threads_pithc,
lv_female_threads.depth AS female_threads_depth,
lv_female_threads.male_length AS female_threads_male_length,
lv_female_threads.min_body_length AS female_threads_min_body_length,
lv_female_threads.min_full_depth AS female_threads_min_full_depth,
lv_female_threads.threads_per_uom AS female_threads_threads_per_uom,
lv_female_threads.screw_id AS female_threads_screw_id,
lv_male_threads.male_thread_id AS male_threads_id,
lv_male_threads.code AS male_threads_code,
lv_male_threads.thread_size AS male_threads_thread_size,
lv_male_threads.female_thread_description AS male_threads_description,
lv_male_threads.clearance_hole_id AS male_threads_clearance_hole_id,
lv_male_threads.tolerance_id AS male_threads_tolerance_id,
lv_male_threads.unit_of_measurement_id AS male_threads_unit_of_measurement_id,
lv_male_threads.thread_series_id AS male_threads_thread_series_id,
lv_male_threads.status_id AS male_threads_status_id,
lv_male_threads.is_web AS male_threads_is_web,
lv_male_threads.web_name AS male_threads_web_name,
lv_male_threads.rank AS male_threads_rank,
lv_male_threads.major_dia AS male_threads_major_dia,
lv_male_threads.minor_dia AS male_threads_minor_dia,
lv_male_threads.pitch AS male_threads_pitch,
lv_male_threads.depth AS male_threads_depth,
lv_male_threads.male_length AS male_threads_male_length,
lv_male_threads.min_body_length AS male_threads_min_body_length,
lv_male_threads.min_full_depth AS male_threads_min_full_depth,
lv_male_threads.threads_per_uom AS male_threads_threads_per_uom,
lv_male_threads.screw_id AS male_threads_screw_id
FROM (((((((lv_partbaseparts
JOIN lv_materials ON ((lv_partbaseparts.part_base_parts_id = lv_materials.id)))
JOIN lv_lengths ON ((lv_partbaseparts.part_base_parts_id = lv_lengths.id)))
JOIN lv_ods ON ((lv_partbaseparts.part_base_parts_id = lv_ods.id)))
JOIN lv_profiles ON ((lv_partbaseparts.part_base_parts_id = lv_profiles.id)))
JOIN lv_unit_of_measurements ON ((lv_partbaseparts.part_base_parts_id = lv_unit_of_measurements.id)))
JOIN lv_female_threads ON ((lv_partbaseparts.part_base_parts_id = lv_female_threads.id)))
JOIN lv_male_threads ON ((lv_partbaseparts.part_base_parts_id = lv_male_threads.id)))
WHERE (lv_partbaseparts.part_type_id = (5)::numeric)


Sadly, nearly every column in the final select statement is needed. In querying against this view, I am finding that it takes about 1 second to return 140 records. I'm looking to see if this time can decrease. Below is my explain plan. It would help me out if somebody could provide some direction on how I might increase the performance. The explain plan can also be viewed from this URL (https://explain.depesz.com/s/cnLq)

"Sort (cost=659.79..659.80 rows=1 width=5068) (actual time=745.091..745.113 rows=288 loops=1)"
" Sort Key: ((((((m.materials_code)::text || (m.part_base_parts_code)::text) || '-'::text) || to_char(m.lengths_millimeters, 'FM09.0'::text)) || '-00'::text))"
" Sort Method: quicksort Memory: 200kB"
" -> Subquery Scan on m (cost=482.81..659.78 rows=1 width=5068) (actual time=101.392..742.794 rows=288 loops=1)"
" -> Nested Loop (cost=482.81..659.76 rows=1 width=5068) (actual time=101.329..740.403 rows=288 loops=1)"
" Join Filter: (lv_partbaseparts.part_base_parts_id = lv_unit_of_measurements.id)"
" Rows Removed by Join Filter: 40218"
" CTE lv_materials"
" -> Hash Join (cost=8.42..58.89 rows=1159 width=981) (actual time=0.196..1.528 rows=1159 loops=1)"
" Hash Cond: (parts_to_materials.material_id = materials.id)"
" -> Hash Join (cost=7.15..41.68 rows=1159 width=10) (actual time=0.122..0.898 rows=1159 loops=1)"
" Hash Cond: (parts_to_materials.part_base_part_id = part_base_parts.id)"
" -> Seq Scan on parts_to_materials (cost=0.00..18.59 rows=1159 width=10) (actual time=0.007..0.216 rows=1159 loops=1)"
" -> Hash (cost=4.29..4.29 rows=229 width=5) (actual time=0.096..0.096 rows=229 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 9kB"
" -> Seq Scan on part_base_parts (cost=0.00..4.29 rows=229 width=5) (actual time=0.006..0.039 rows=229 loops=1)"
" -> Hash (cost=1.12..1.12 rows=12 width=976) (actual time=0.044..0.044 rows=12 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on materials (cost=0.00..1.12 rows=12 width=976) (actual time=0.030..0.031 rows=12 loops=1)"
" CTE lv_partbaseparts"
" -> Seq Scan on part_base_parts part_base_parts_1 (cost=0.00..4.29 rows=229 width=20) (actual time=0.007..0.116 rows=229 loops=1)"
" CTE lv_female_threads"
" -> Nested Loop (cost=15.81..20.94 rows=2 width=231) (actual time=0.198..1.525 rows=208 loops=1)"
" -> Nested Loop (cost=15.67..20.43 rows=2 width=231) (actual time=0.134..0.917 rows=208 loops=1)"
" -> Hash Join (cost=15.53..18.07 rows=1 width=226) (actual time=0.036..0.079 rows=39 loops=1)"
" Hash Cond: (threads.unit_of_measurement_id = unit_of_measurement_suffixes.unit_of_measurement_id)"
" -> Seq Scan on threads (cost=0.00..2.39 rows=39 width=108) (actual time=0.004..0.018 rows=39 loops=1)"
" -> Hash (cost=15.50..15.50 rows=2 width=130) (actual time=0.020..0.020 rows=2 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on unit_of_measurement_suffixes (cost=0.00..15.50 rows=2 width=130) (actual time=0.016..0.018 rows=2 loops=1)"
" Filter: (type = 3::numeric)"
" Rows Removed by Filter: 8"
" -> Index Only Scan using parts_to_threads_female_idx on parts_to_threads_female (cost=0.14..2.25 rows=11 width=10) (actual time=0.018..0.020 rows=5 loops=39)"
" Index Cond: (thread_id = threads.id)"
" Heap Fetches: 208"
" -> Index Only Scan using part_base_parts_id_key on part_base_parts part_base_parts_2 (cost=0.14..0.24 rows=1 width=5) (actual time=0.002..0.002 rows=1 loops=208)"
" Index Cond: (id = parts_to_threads_female.part_base_part_id)"
" Heap Fetches: 208"
" CTE lv_male_threads"
" -> Nested Loop (cost=15.81..19.39 rows=1 width=231) (actual time=0.117..1.114 rows=55 loops=1)"
" -> Nested Loop (cost=15.67..18.93 rows=1 width=231) (actual time=0.102..0.422 rows=55 loops=1)"
" -> Hash Join (cost=15.53..18.07 rows=1 width=226) (actual time=0.030..0.081 rows=39 loops=1)"
" Hash Cond: (threads_1.unit_of_measurement_id = unit_of_measurement_suffixes_1.unit_of_measurement_id)"
" -> Seq Scan on threads threads_1 (cost=0.00..2.39 rows=39 width=108) (actual time=0.002..0.017 rows=39 loops=1)"
" -> Hash (cost=15.50..15.50 rows=2 width=130) (actual time=0.012..0.012 rows=2 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on unit_of_measurement_suffixes unit_of_measurement_suffixes_1 (cost=0.00..15.50 rows=2 width=130) (actual time=0.007..0.007 rows=2 loops=1)"
" Filter: (type = 3::numeric)"
" Rows Removed by Filter: 8"
" -> Index Only Scan using parts_to_threads_pkey on parts_to_threads_male (cost=0.14..0.82 rows=4 width=10) (actual time=0.007..0.008 rows=1 loops=39)"
" Index Cond: (thread_id = threads_1.id)"
" Heap Fetches: 45"
" -> Index Only Scan using part_base_parts_id_key on part_base_parts part_base_parts_3 (cost=0.14..0.45 rows=1 width=5) (actual time=0.011..0.011 rows=1 loops=55)"
" Index Cond: (id = parts_to_threads_male.part_base_part_id)"
" Heap Fetches: 55"
" CTE lv_lengths"
" -> Hash Join (cost=41.63..275.71 rows=5442 width=54) (actual time=0.438..9.365 rows=5442 loops=1)"
" Hash Cond: (parts_to_measurements.measurement_id = measurements.id)"
" -> Hash Join (cost=7.15..166.40 rows=5442 width=10) (actual time=0.110..5.338 rows=5442 loops=1)"
" Hash Cond: (parts_to_measurements.part_base_part_id = part_base_parts_4.id)"
" -> Seq Scan on parts_to_measurements (cost=0.00..84.42 rows=5442 width=10) (actual time=0.017..1.632 rows=5442 loops=1)"
" -> Hash (cost=4.29..4.29 rows=229 width=5) (actual time=0.078..0.078 rows=229 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 9kB"
" -> Seq Scan on part_base_parts part_base_parts_4 (cost=0.00..4.29 rows=229 width=5) (actual time=0.003..0.022 rows=229 loops=1)"
" -> Hash (cost=24.77..24.77 rows=777 width=49) (actual time=0.315..0.315 rows=777 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 66kB"
" -> Seq Scan on measurements (cost=0.00..24.77 rows=777 width=49) (actual time=0.002..0.084 rows=777 loops=1)"
" CTE lv_ods"
" -> Hash Join (cost=41.61..52.18 rows=228 width=54) (actual time=0.527..0.817 rows=228 loops=1)"
" Hash Cond: (parts_to_ods.measurement_id = measurements_1.id)"
" -> Hash Join (cost=7.13..14.56 rows=228 width=10) (actual time=0.103..0.262 rows=228 loops=1)"
" Hash Cond: (part_base_parts_5.id = parts_to_ods.part_base_part_id)"
" -> Seq Scan on part_base_parts part_base_parts_5 (cost=0.00..4.29 rows=229 width=5) (actual time=0.002..0.034 rows=229 loops=1)"
" -> Hash (cost=4.28..4.28 rows=228 width=10) (actual time=0.073..0.073 rows=228 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on parts_to_ods (cost=0.00..4.28 rows=228 width=10) (actual time=0.005..0.025 rows=228 loops=1)"
" -> Hash (cost=24.77..24.77 rows=777 width=49) (actual time=0.412..0.412 rows=777 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 66kB"
" -> Seq Scan on measurements measurements_1 (cost=0.00..24.77 rows=777 width=49) (actual time=0.021..0.195 rows=777 loops=1)"
" CTE lv_profiles"
" -> Hash Join (cost=21.88..32.46 rows=229 width=361) (actual time=0.147..0.372 rows=229 loops=1)"
" Hash Cond: (parts_to_profiles.profile_id = profiles.id)"
" -> Hash Join (cost=7.15..14.59 rows=229 width=10) (actual time=0.120..0.243 rows=229 loops=1)"
" Hash Cond: (part_base_parts_6.id = parts_to_profiles.part_base_part_id)"
" -> Seq Scan on part_base_parts part_base_parts_6 (cost=0.00..4.29 rows=229 width=5) (actual time=0.005..0.025 rows=229 loops=1)"
" -> Hash (cost=4.29..4.29 rows=229 width=10) (actual time=0.089..0.089 rows=229 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on parts_to_profiles (cost=0.00..4.29 rows=229 width=10) (actual time=0.006..0.034 rows=229 loops=1)"
" -> Hash (cost=12.10..12.10 rows=210 width=356) (actual time=0.016..0.016 rows=4 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on profiles (cost=0.00..12.10 rows=210 width=356) (actual time=0.010..0.010 rows=4 loops=1)"
" CTE lv_unit_of_measurements"
" -> Hash Join (cost=8.27..18.85 rows=229 width=647) (actual time=0.115..0.351 rows=229 loops=1)"
" Hash Cond: (parts_to_unit_of_measurements.unit_of_measurement_id = unit_of_measurements.id)"
" -> Hash Join (cost=7.15..14.59 rows=229 width=10) (actual time=0.086..0.213 rows=229 loops=1)"
" Hash Cond: (part_base_parts_7.id = parts_to_unit_of_measurements.part_base_part_id)"
" -> Seq Scan on part_base_parts part_base_parts_7 (cost=0.00..4.29 rows=229 width=5) (actual time=0.003..0.022 rows=229 loops=1)"
" -> Hash (cost=4.29..4.29 rows=229 width=10) (actual time=0.069..0.069 rows=229 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 10kB"
" -> Seq Scan on parts_to_unit_of_measurements (cost=0.00..4.29 rows=229 width=10) (actual time=0.005..0.018 rows=229 loops=1)"
" -> Hash (cost=1.05..1.05 rows=5 width=642) (actual time=0.017..0.017 rows=5 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 1kB"
" -> Seq Scan on unit_of_measurements (cost=0.00..1.05 rows=5 width=642) (actual time=0.012..0.012 rows=5 loops=1)"
" -> Nested Loop (cost=0.10..171.88 rows=1 width=4510) (actual time=7.020..689.257 rows=942 loops=1)"
" Join Filter: (lv_partbaseparts.part_base_parts_id = lv_profiles.id)"
" Rows Removed by Join Filter: 200154"
" -> Nested Loop (cost=0.10..166.72 rows=1 width=4140) (actual time=6.858..571.493 rows=1596 loops=1)"
" Join Filter: (lv_partbaseparts.part_base_parts_id = lv_materials.id)"
" Rows Removed by Join Filter: 128976"
" -> Nested Loop (cost=0.10..140.57 rows=1 width=3150) (actual time=4.523..163.426 rows=1674 loops=1)"
" Join Filter: (lv_lengths.id = lv_partbaseparts.part_base_parts_id)"
" Rows Removed by Join Filter: 72466"
" -> Nested Loop (cost=0.07..10.68 rows=1 width=1638) (actual time=2.623..19.278 rows=44 loops=1)"
" Join Filter: (lv_ods.id = lv_partbaseparts.part_base_parts_id)"
" Rows Removed by Join Filter: 15348"
" -> CTE Scan on lv_partbaseparts (cost=0.00..5.15 rows=1 width=126) (actual time=0.015..0.322 rows=74 loops=1)"
" Filter: (part_type_id = 5::numeric)"
" Rows Removed by Filter: 155"
" -> Hash Join (cost=0.07..5.50 rows=2 width=1512) (actual time=0.037..0.221 rows=208 loops=74)"
" Hash Cond: (lv_ods.id = lv_female_threads.id)"
" -> CTE Scan on lv_ods (cost=0.00..4.56 rows=228 width=466) (actual time=0.007..0.039 rows=228 loops=74)"
" -> Hash (cost=0.04..0.04 rows=2 width=1046) (actual time=2.049..2.049 rows=208 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 33kB"
" -> CTE Scan on lv_female_threads (cost=0.00..0.04 rows=2 width=1046) (actual time=0.203..1.846 rows=208 loops=1)"
" -> Hash Join (cost=0.03..129.55 rows=27 width=1512) (actual time=0.047..2.949 rows=1685 loops=44)"
" Hash Cond: (lv_lengths.id = lv_male_threads.id)"
" -> CTE Scan on lv_lengths (cost=0.00..108.84 rows=5442 width=466) (actual time=0.010..0.966 rows=5442 loops=44)"
" -> Hash (cost=0.02..0.02 rows=1 width=1046) (actual time=1.392..1.392 rows=55 loops=1)"
" Buckets: 1024 Batches: 1 Memory Usage: 9kB"
" -> CTE Scan on lv_male_threads (cost=0.00..0.02 rows=1 width=1046) (actual time=0.120..1.225 rows=55 loops=1)"
" -> CTE Scan on lv_materials (cost=0.00..26.08 rows=6 width=990) (actual time=0.003..0.229 rows=78 loops=1674)"
" Filter: (materials_id = 6::numeric)"
" Rows Removed by Filter: 1081"
" -> CTE Scan on lv_profiles (cost=0.00..5.15 rows=1 width=370) (actual time=0.001..0.051 rows=126 loops=1596)"
" Filter: (profiles_id = 1::numeric)"
" Rows Removed by Filter: 103"
" -> CTE Scan on lv_unit_of_measurements (cost=0.00..5.15 rows=1 width=656) (actual time=0.002..0.047 rows=43 loops=942)"
" Filter: (unit_of_measurements_id = 2::numeric)"
" Rows Removed by Filter: 186"
"Total runtime: 746.264 ms"

Answer

It seems to me like most of the time is spent performing nested loop joins that were chosen because the row count for some of the CTEs were wildly underestimated.

I won't try and got through that massive query in detail, but there are three things that you could try:

  1. Don't write the query with CTEs. Instead of

    WITH x AS (SELECT ...)
    SELECT ... FROM x
    

    you might try to write

    SELECT ... FROM (SELECT ...) x
    

    Since WITH acts as an optimization barrier, PostgreSQL might be able to figure out a better plan that way.

  2. Consider increasing join_collapse_limit (and, if you rewrite the query, maybe from_collapse_limit) beyond the default value of 8. The planner will take longer that way but may come up with a better plan.

  3. The brutal way: set enable_nestloop to off and force the planner to use hash or merge joins. Maybe the consequence of the misestimates won't be so bad that way.