0

I have the following query:

select i.id as id, 
       i.ts_updated as updated, 
       i."name" as name, 
       cast(attr.data_norm as text) as data_norm, 
       cast(_59.json_agg as text) as _59, 
       attr.num_ports as num_ports,
       cast(_58.json_agg as text) as _58, 
       cast(_60.json_agg as text) as _60, 
       attr.frequency as frequency, 
       cast(attr.data as text) as data, 
       attr.r_ref as r_ref, 
       attr.frequency_hz as frequency_hz, 
       cast(tags_array as text) as tags
from bo_instance i 
left join attrib_touchstone attr on i.id=attr.bo_instance_id
left join ( select x_boi_tag.bo_instance_id as instance_id, 
                   json_agg(tag."name" order by tag."name") as tags_array 
            from x_boi_tag 
            left join tag on x_boi_tag.tag_id=tag.id 
            where tag.is_deleted = false 
            group by instance_id ) t on t.instance_id=i.id 
left join ( select x.bo_instance_id, 
                   json_agg(json_build_object('name', boAttr."name", 'value', v."name")) as value_list 
            from x_ia_value_list x 
            left join bo_attribute_value v on v.id=x.bo_attribute_value_id 
            left join bo_class_attribute boAttr on v.bo_class_attribute_id = boAttr.id 
            group by x.bo_instance_id ) val on val.bo_instance_id = i.id 
left join lateral ( select json_agg(value #>> '{value}' order by value #>> '{value}')  
                    from json_array_elements(val.value_list) 
                    where value #>> '{name}' = 'freq_units') as _59 on true 
left join lateral ( select json_agg(value #>> '{value}' order by value #>> '{value}')  
                    from json_array_elements(val.value_list) 
                    where value #>> '{name}' = 'parameter_type') as _58 on true 
left join lateral ( select json_agg(value #>> '{value}' order by value #>> '{value}')  
                    from json_array_elements(val.value_list) 
                    where value #>> '{name}' = 'format') as _60 on true 
where i.is_deleted=false 
  and i.bo_class_id=34 
  and true 
order by i.id desc limit 150

The execution took 22 seconds, which in my opinion is pretty slow.

Here is the query plan:

Limit  (cost=869494.67..870365.34 rows=150 width=285) (actual time=25585.374..25612.329 rows=150 loops=1)
  Output: i.id, i.ts_updated, i.name, ((attr.data_norm)::text), (((json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[]))))::text), attr.num_ports, (((json_agg((json_array_elements_1.value #>> '{value}'::text[]) ORDER BY (json_array_elements_1.value #>> '{value}'::text[]))))::text), (((json_agg((json_array_elements_2.value #>> '{value}'::text[]) ORDER BY (json_array_elements_2.value #>> '{value}'::text[]))))::text), attr.frequency, ((attr.data)::text), attr.r_ref, attr.frequency_hz, ((t.tags_array)::text)
  Buffers: shared hit=38064, temp read=41731 written=63158
  ->  Nested Loop Left Join  (cost=869494.67..7900133.26 rows=1211245 width=285) (actual time=25585.372..25605.884 rows=150 loops=1)
        Output: i.id, i.ts_updated, i.name, (attr.data_norm)::text, ((json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[]))))::text, attr.num_ports, ((json_agg((json_array_elements_1.value #>> '{value}'::text[]) ORDER BY (json_array_elements_1.value #>> '{value}'::text[]))))::text, ((json_agg((json_array_elements_2.value #>> '{value}'::text[]) ORDER BY (json_array_elements_2.value #>> '{value}'::text[]))))::text, attr.frequency, (attr.data)::text, attr.r_ref, attr.frequency_hz, (t.tags_array)::text
        Buffers: shared hit=37674, temp read=41731 written=63158
        ->  Nested Loop Left Join  (cost=869493.16..5998478.61 rows=1211245 width=1707) (actual time=25585.330..25589.575 rows=150 loops=1)
              Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz, t.tags_array, val.value_list, (json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[]))), (json_agg((json_array_elements_1.value #>> '{value}'::text[]) ORDER BY (json_array_elements_1.value #>> '{value}'::text[])))
              Buffers: shared hit=37674, temp read=41731 written=63158
              ->  Nested Loop Left Join  (cost=869491.65..4133161.31 rows=1211245 width=1675) (actual time=25585.311..25588.171 rows=150 loops=1)
                    Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz, t.tags_array, val.value_list, (json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[])))
                    Buffers: shared hit=37674, temp read=41731 written=63158
                    ->  Merge Left Join  (cost=869490.14..2267844.01 rows=1211245 width=1643) (actual time=25585.257..25586.503 rows=150 loops=1)
                          Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz, t.tags_array, val.value_list
                          Inner Unique: true
                          Merge Cond: (i.id = val.bo_instance_id)
                          Buffers: shared hit=37674, temp read=41731 written=63158
                          ->  Merge Left Join  (cost=1046.86..1380457.54 rows=1211245 width=1611) (actual time=9.411..10.418 rows=150 loops=1)
                                Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz, t.tags_array
                                Inner Unique: true
                                Merge Cond: (i.id = t.instance_id)
                                Buffers: shared hit=3771
                                ->  Gather Merge  (cost=1000.88..1377380.87 rows=1211245 width=1579) (actual time=7.744..14.986 rows=150 loops=1)
                                      Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz
                                      Workers Planned: 2
                                      Workers Launched: 2
                                      Buffers: shared hit=4154
                                      ->  Nested Loop Left Join  (cost=0.85..1236573.12 rows=504685 width=1579) (actual time=1.129..1.527 rows=80 loops=3)
                                            Output: i.id, i.ts_updated, i.name, attr.data_norm, attr.num_ports, attr.frequency, attr.data, attr.r_ref, attr.frequency_hz
                                            Buffers: shared hit=4154
                                            Worker 0: actual time=0.016..0.200 rows=42 loops=1
                                              Buffers: shared hit=182
                                            Worker 1: actual time=0.016..0.228 rows=48 loops=1
                                              Buffers: shared hit=208
                                            ->  Parallel Index Scan Backward using bo_instance_pkey on public.bo_instance i  (cost=0.43..536630.17 rows=504685 width=66) (actual time=0.883..0.953 rows=80 loops=3)
                                                  Output: i.id, i.search_text, i.bo_class_id, i.ts_created, i.ts_updated, i.updated_by_user, i.updated_by_process, i.name, i.is_deleted
                                                  Filter: ((NOT i.is_deleted) AND (i.bo_class_id = 34))
                                                  Rows Removed by Filter: 1155
                                                  Buffers: shared hit=3192
                                                  Worker 0: actual time=0.007..0.037 rows=42 loops=1
                                                    Buffers: shared hit=13
                                                  Worker 1: actual time=0.007..0.042 rows=48 loops=1
                                                    Buffers: shared hit=15
                                            ->  Index Scan using idx_attrib_touchstone_bo_instance_id on public.attrib_touchstone attr  (cost=0.43..1.38 rows=1 width=1521) (actual time=0.005..0.006 rows=1 loops=240)
                                                  Output: attr.id, attr.ts_created, attr.ts_updated, attr.updated_by_user, attr.updated_by_process, attr.bo_instance_id, attr.bo_class_id, attr.num_ports, attr.r_ref, attr.frequency, attr.frequency_hz, attr.data, attr.data_norm
                                                  Index Cond: (i.id = attr.bo_instance_id)
                                                  Buffers: shared hit=962
                                                  Worker 0: actual time=0.002..0.002 rows=1 loops=42
                                                    Buffers: shared hit=169
                                                  Worker 1: actual time=0.002..0.002 rows=1 loops=48
                                                    Buffers: shared hit=193
                                ->  Sort  (cost=45.98..46.43 rows=180 width=40) (actual time=1.664..1.665 rows=1 loops=1)
                                      Output: t.tags_array, t.instance_id
                                      Sort Key: t.instance_id DESC
                                      Sort Method: quicksort  Memory: 41kB
                                      Buffers: shared hit=7
                                      ->  Subquery Scan on t  (cost=32.18..39.24 rows=180 width=40) (actual time=0.735..1.577 rows=191 loops=1)
                                            Output: t.tags_array, t.instance_id
                                            Buffers: shared hit=7
                                            ->  GroupAggregate  (cost=32.18..37.44 rows=180 width=40) (actual time=0.733..1.489 rows=191 loops=1)
                                                  Output: x_boi_tag.bo_instance_id, json_agg(tag.name ORDER BY tag.name)
                                                  Group Key: x_boi_tag.bo_instance_id
                                                  Buffers: shared hit=7
                                                  ->  Sort  (cost=32.18..33.18 rows=401 width=14) (actual time=0.715..0.814 rows=462 loops=1)
                                                        Output: x_boi_tag.bo_instance_id, tag.name
                                                        Sort Key: x_boi_tag.bo_instance_id
                                                        Sort Method: quicksort  Memory: 51kB
                                                        Buffers: shared hit=7
                                                        ->  Hash Join  (cost=4.19..14.84 rows=401 width=14) (actual time=0.111..0.521 rows=462 loops=1)
                                                              Output: x_boi_tag.bo_instance_id, tag.name
                                                              Inner Unique: true
                                                              Hash Cond: (x_boi_tag.tag_id = tag.id)
                                                              Buffers: shared hit=7
                                                              ->  Seq Scan on public.x_boi_tag  (cost=0.00..9.44 rows=444 width=16) (actual time=0.009..0.144 rows=462 loops=1)
                                                                    Output: x_boi_tag.id, x_boi_tag.bo_instance_id, x_boi_tag.tag_id, x_boi_tag.ts_created, x_boi_tag.ts_updated, x_boi_tag.updated_by_user, x_boi_tag.updated_by_process
                                                                    Buffers: shared hit=5
                                                              ->  Hash  (cost=3.03..3.03 rows=93 width=14) (actual time=0.094..0.095 rows=82 loops=1)
                                                                    Output: tag.name, tag.id
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 13kB
                                                                    Buffers: shared hit=2
                                                                    ->  Seq Scan on public.tag  (cost=0.00..3.03 rows=93 width=14) (actual time=0.005..0.059 rows=82 loops=1)
                                                                          Output: tag.name, tag.id
                                                                          Filter: (NOT tag.is_deleted)
                                                                          Rows Removed by Filter: 22
                                                                          Buffers: shared hit=2
                          ->  Sort  (cost=868443.28..871232.88 rows=1115840 width=40) (actual time=25575.835..25575.910 rows=156 loops=1)
                                Output: val.value_list, val.bo_instance_id
                                Sort Key: val.bo_instance_id DESC
                                Sort Method: external merge  Disk: 172768kB
                                Buffers: shared hit=33903, temp read=41731 written=63158
                                ->  Subquery Scan on val  (cost=633959.56..695332.83 rows=1115840 width=40) (actual time=10203.379..23976.330 rows=1209626 loops=1)
                                      Output: val.value_list, val.bo_instance_id
                                      Buffers: shared hit=33903, temp read=14082 written=14082
                                      ->  GroupAggregate  (cost=633959.56..684174.43 rows=1115840 width=40) (actual time=10203.378..23208.688 rows=1209626 loops=1)
                                            Output: x.bo_instance_id, json_agg(json_build_object('name', boattr.name, 'value', v.name))
                                            Group Key: x.bo_instance_id
                                            Buffers: shared hit=33903, temp read=14082 written=14082
                                            ->  Sort  (cost=633959.56..643026.28 rows=3626687 width=23) (actual time=10203.356..11984.566 rows=3626608 loops=1)
                                                  Output: x.bo_instance_id, boattr.name, v.name
                                                  Sort Key: x.bo_instance_id
                                                  Sort Method: external merge  Disk: 112656kB
                                                  Buffers: shared hit=33903, temp read=14082 written=14082
                                                  ->  Hash Left Join  (cost=11.09..90071.01 rows=3626687 width=23) (actual time=0.181..6299.137 rows=3626608 loops=1)
                                                        Output: x.bo_instance_id, boattr.name, v.name
                                                        Inner Unique: true
                                                        Hash Cond: (v.bo_class_attribute_id = boattr.id)
                                                        Buffers: shared hit=33903
                                                        ->  Hash Left Join  (cost=3.51..80289.63 rows=3626687 width=22) (actual time=0.073..3898.956 rows=3626608 loops=1)
                                                              Output: x.bo_instance_id, v.name, v.bo_class_attribute_id
                                                              Inner Unique: true
                                                              Hash Cond: (x.bo_attribute_value_id = v.id)
                                                              Buffers: shared hit=33899
                                                              ->  Seq Scan on public.x_ia_value_list x  (cost=0.00..70163.87 rows=3626687 width=16) (actual time=0.006..1242.323 rows=3626608 loops=1)
                                                                    Output: x.id, x.bo_attribute_value_id, x.bo_instance_id, x.ts_created, x.ts_updated, x.updated_by_user, x.updated_by_process
                                                                    Buffers: shared hit=33897
                                                              ->  Hash  (cost=2.67..2.67 rows=67 width=22) (actual time=0.060..0.061 rows=83 loops=1)
                                                                    Output: v.name, v.id, v.bo_class_attribute_id
                                                                    Buckets: 1024  Batches: 1  Memory Usage: 13kB
                                                                    Buffers: shared hit=2
                                                                    ->  Seq Scan on public.bo_attribute_value v  (cost=0.00..2.67 rows=67 width=22) (actual time=0.003..0.031 rows=83 loops=1)
                                                                          Output: v.name, v.id, v.bo_class_attribute_id
                                                                          Buffers: shared hit=2
                                                        ->  Hash  (cost=5.59..5.59 rows=159 width=17) (actual time=0.103..0.103 rows=159 loops=1)
                                                              Output: boattr.name, boattr.id
                                                              Buckets: 1024  Batches: 1  Memory Usage: 17kB
                                                              Buffers: shared hit=4
                                                              ->  Seq Scan on public.bo_class_attribute boattr  (cost=0.00..5.59 rows=159 width=17) (actual time=0.003..0.050 rows=159 loops=1)
                                                                    Output: boattr.name, boattr.id
                                                                    Buffers: shared hit=4
                    ->  Aggregate  (cost=1.51..1.52 rows=1 width=32) (actual time=0.010..0.010 rows=1 loops=150)
                          Output: json_agg((json_array_elements.value #>> '{value}'::text[]) ORDER BY (json_array_elements.value #>> '{value}'::text[]))
                          ->  Function Scan on pg_catalog.json_array_elements  (cost=0.00..1.50 rows=1 width=32) (actual time=0.005..0.006 rows=1 loops=150)
                                Output: json_array_elements.value
                                Function Call: json_array_elements(val.value_list)
                                Filter: ((json_array_elements.value #>> '{name}'::text[]) = 'freq_units'::text)
                                Rows Removed by Filter: 2
              ->  Aggregate  (cost=1.51..1.52 rows=1 width=32) (actual time=0.008..0.008 rows=1 loops=150)
                    Output: json_agg((json_array_elements_1.value #>> '{value}'::text[]) ORDER BY (json_array_elements_1.value #>> '{value}'::text[]))
                    ->  Function Scan on pg_catalog.json_array_elements json_array_elements_1  (cost=0.00..1.50 rows=1 width=32) (actual time=0.004..0.004 rows=1 loops=150)
                          Output: json_array_elements_1.value
                          Function Call: json_array_elements(val.value_list)
                          Filter: ((json_array_elements_1.value #>> '{name}'::text[]) = 'parameter_type'::text)
                          Rows Removed by Filter: 2
        ->  Aggregate  (cost=1.51..1.52 rows=1 width=32) (actual time=0.040..0.040 rows=1 loops=150)
              Output: json_agg((json_array_elements_2.value #>> '{value}'::text[]) ORDER BY (json_array_elements_2.value #>> '{value}'::text[]))
              ->  Function Scan on pg_catalog.json_array_elements json_array_elements_2  (cost=0.00..1.50 rows=1 width=32) (actual time=0.003..0.004 rows=1 loops=150)
                    Output: json_array_elements_2.value
                    Function Call: json_array_elements(val.value_list)
                    Filter: ((json_array_elements_2.value #>> '{name}'::text[]) = 'format'::text)
                    Rows Removed by Filter: 2
Planning time: 1.027 ms
Execution time: 25728.499 ms

I am not that familiar with query optimization. Any suggestions are welcome. At least can you point out where I have to create additional indexes? Thank you in advance.

0

You are spending a huge amount of time packaging up subquery "x", just to pull 150 single-valued results out of it. Can you move the subquery over "x" out of the join, and into the select list? Or perhaps, into a lateral join? That way it can fish out and package up the data for the specific rows it is going to return.

Without CREATE statements for all the tables, indexes, and constraints, it is hard to propose an exact syntax for you to use, as I have no easy way of testing it out.

Also, are all those left joins really necessary? If you don't need to do anything with all the implied NULLs, maybe you can just drop some of the 'left's.

0

I'd say that the problem is the gross mis-estimation of the index scan on public.bo_instance:

->  Parallel Index Scan Backward using bo_instance_pkey on public.bo_instance i
                (cost=0.43..536630.17 rows=504685 width=66)
                (actual time=0.883..0.953 rows=80 loops=3)
      Filter: ((NOT i.is_deleted) AND (i.bo_class_id = 34))
      Rows Removed by Filter: 1155
      Buffers: shared hit=3192

The statistics seem way off. The table also seems quite bloated; I'd run first

VACUUM (FULL) public.bo_instance;

and then

VACUUM (ANALYZE) public.bo_instance;

to fix that.

If PostgreSQL gets that estimate right, it might choose a Nested Loop join and be way faster.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.