Re: Select on partitioned table is very slow

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 


Dear Depesz, Laurenz,

Thanks very much for the fast responses. They are actually correct and saved me a lot of time. I couldn't test the cast from the Java test but this is something I can deal with later on (most probably updating the column types to text in the database side instead). But what I could do was reproduce the same problem in the psql console using the cast in the other way. This sentence:

explain analyze select logical_identifier, version_id, lastproduct
    FROM test_product_ui_partition.product_ui pui
    WHERE pui.mission_id='urn:esa:psa:context:investigation:mission.em16'::citext
    AND pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::citext;

Creates an output equivalent to that returned from the JAVA application and reproduces the exact same problems: Scans all the partitions instead of select the right one and uses sec scans for all the cases.
Attached the result.

Again, many thanks for your help,
Jose Osinde

explain analyze select logical_identifier, version_id, lastproduct 
   FROM test_product_ui_partition.product_ui pui 
      WHERE pui.mission_id='urn:esa:psa:context:investigation:mission.em16'::text 
      AND pui.logical_identifier='urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text;  


 Gather  (cost=1000.00..5617399.10 rows=19 width=82) (actual time=10485.082..10497.268 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append  (cost=0.00..5616397.20 rows=15 width=82) (actual time=9221.542..10479.134 rows=0 loops=3)
         ->  Parallel Seq Scan on product_ui_em16 pui_10  (cost=0.00..2603849.81 rows=3 width=112) (actual time=3883.905..5141.463 rows=0 loops=3)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 5337106
         ->  Parallel Seq Scan on product_ui_rosetta pui_6  (cost=0.00..2382752.79 rows=1 width=57) (actual time=6213.704..6213.704 rows=0 loops=2)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 5878988
         ->  Parallel Seq Scan on product_ui_mex pui_7  (cost=0.00..354434.31 rows=1 width=56) (actual time=1417.182..1417.186 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 1085822
         ->  Parallel Seq Scan on product_ui_smart1 pui_8  (cost=0.00..148741.56 rows=1 width=63) (actual time=1125.713..1125.713 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 1187167
         ->  Parallel Seq Scan on product_ui_vex pui_13  (cost=0.00..112914.84 rows=1 width=56) (actual time=919.386..919.386 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 1133382
         ->  Parallel Seq Scan on product_ui_bc pui_9  (cost=0.00..8890.52 rows=1 width=83) (actual time=78.910..78.910 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 67763
         ->  Parallel Seq Scan on product_ui_ch1 pui_2  (cost=0.00..2224.51 rows=1 width=59) (actual time=20.162..20.162 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 20345
         ->  Parallel Seq Scan on product_ui_huygens pui_1  (cost=0.00..2090.82 rows=1 width=70) (actual time=19.724..19.727 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 19700
         ->  Parallel Seq Scan on product_ui_ground_based pui_3  (cost=0.00..260.74 rows=1 width=75) (actual time=2.105..2.105 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 2237
         ->  Parallel Seq Scan on product_ui_giotto pui_4  (cost=0.00..209.79 rows=1 width=47) (actual time=2.089..2.089 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 2129
         ->  Parallel Seq Scan on product_ui_juice pui_12  (cost=0.00..13.88 rows=1 width=67) (actual time=0.123..0.123 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 100
         ->  Parallel Seq Scan on product_ui_emrsp pui_11  (cost=0.00..10.26 rows=1 width=65) (actual time=0.000..0.000 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
         ->  Parallel Seq Scan on product_ui_hubble pui_5  (cost=0.00..3.29 rows=1 width=62) (actual time=0.054..0.054 rows=0 loops=1)
               Filter: (((mission_id)::text = 'urn:esa:psa:context:investigation:mission.em16'::text) AND ((logical_identifier)::text = 'urn:esa:psa:em16_tgo_frd:data_raw:frd_raw_sc_n_20220729t000000-20220729t235959'::text))
               Rows Removed by Filter: 33
 Planning Time: 17.643 ms
 Execution Time: 10497.523 ms
(44 rows)
[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux