当前位置： 文江博客话题详情

Postgres 视图需要很长时间才能执行

发布于 2025-01-11 09:46:56 字数 24230 浏览 0 评论 0原文

我创建了一个视图，随后用它来生成一些报告。 第一次创建它时，我的数据量很小，没有注意到任何问题。

现在，该视图需要很长时间才能加载，并导致许多性能问题。 我试图以这种方式限制视图：

WHERE date_part('year'::text, e.date) >= date_part('year'::text, CURRENT_DATE - '1 year'::interval);

不幸的是，它根本没有帮助。 有没有一种有效的方法来加快我的查询速度？

CREATE OR REPLACE VIEW public.v_lpr
AS WITH e AS (
         SELECT e_1.carpark_id,
            e_1.event_time AS date,
            e_1.device_type,
            e_1.event_type,
            e_1.ticket_type,
            sum(
                CASE
                    WHEN e_1.count > 0 THEN e_1.count
                    ELSE 1
                END) AS sum_event,
            e_1.licenseplatekey
           FROM events e_1
          GROUP BY e_1.carpark_id, e_1.event_type, e_1.device_type, e_1.ticket_type, e_1.event_time, e_1.licenseplatekey
        )
 SELECT e.carpark_id,
    e.date,
    e.device_type,
    COALESCE(ddt.description, 'Unknown'::character varying) AS device_type_name,
    e.event_type,
    COALESCE(det.description, 'Unknown'::character varying) AS event_type_name,
    e.ticket_type,
    COALESCE(dtt.description, 'Other'::character varying) AS ticket_type_name,
    e.sum_event AS count,
    e.licenseplatekey,
    d.country,
    d.state,
    d.district,
    df.facilityname,
    df.tenantname
   FROM e
     JOIN dim_licenseplate d ON d.key = e.licenseplatekey
     JOIN dimfacility df ON df.facilityid = e.carpark_id AND e.date >= date(df.scd_start) AND (e.date < date(df.scd_end) OR df.scd_end IS NULL)
     LEFT JOIN dimtype dtt ON dtt.typeid = e.ticket_type AND dtt.mapping_type::text = 'ticket_type'::text
     LEFT JOIN dimtype ddt ON ddt.typeid = e.device_type AND ddt.mapping_type::text = 'device_type'::text
     LEFT JOIN dimtype det ON det.typeid = e.event_type AND det.mapping_type::text = 'event_type'::text;

查询计划 (https://explain.dalibo.com/plan/lGl< /a>)

Hash Left Join  (cost=10060558.48..11195587.02 rows=5775196 width=204) (actual time=85580.589..507072.608 rows=6734265 loops=1)
  Hash Cond: (e.event_type = det.typeid)
  Buffers: shared hit=6373 read=1118955, temp read=1251698 written=1668112
  CTE e
    ->  Finalize GroupAggregate  (cost=7492458.49..10060413.80 rows=6764099 width=40) (actual time=85578.911..256822.632 rows=67400480 loops=1)
          Group Key: e_1.carpark_id, e_1.event_type, e_1.device_type, e_1.ticket_type, e_1.event_time, e_1.licenseplatekey
          Buffers: shared hit=6289 read=1118955, temp read=1251698 written=1253441
          ->  Gather Merge  (cost=7492458.49..9756029.35 rows=13528198 width=40) (actual time=85578.901..211729.452 rows=67431826 loops=1)
                Workers Planned: 2
                Workers Launched: 2
                Buffers: shared hit=6289 read=1118955, temp read=1251698 written=1253441
                ->  Partial GroupAggregate  (cost=7491458.47..8193539.61 rows=6764099 width=40) (actual time=84935.312..121437.041 rows=22477275 loops=3)
                      Group Key: e_1.carpark_id, e_1.event_type, e_1.device_type, e_1.ticket_type, e_1.event_time, e_1.licenseplatekey
                      Buffers: shared hit=6289 read=1118955, temp read=1251698 written=1253441
                      ->  Sort  (cost=7491458.47..7561951.82 rows=28197340 width=36) (actual time=84935.300..103478.557 rows=22548366 loops=3)
                            Sort Key: e_1.carpark_id, e_1.event_type, e_1.device_type, e_1.ticket_type, e_1.event_time, e_1.licenseplatekey
                            Sort Method: external merge  Disk: 947672kB
                            Buffers: shared hit=6289 read=1118955, temp read=1251698 written=1253441
                            ->  Result  (cost=0.00..1689104.77 rows=28197340 width=36) (actual time=0.066..15116.298 rows=22548366 loops=3)
                                  Buffers: shared hit=6203 read=1118955
                                  ->  Append  (cost=0.00..1407131.37 rows=28197340 width=36) (actual time=0.065..11417.413 rows=22548366 loops=3)
                                        Buffers: shared hit=6203 read=1118955
                                        ->  Parallel Seq Scan on events e_1  (cost=0.00..0.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=3)
                                        ->  Parallel Seq Scan on events_2018_01 e_1_1  (cost=0.00..27341.93 rows=538092 width=36) (actual time=0.063..187.542 rows=430474 loops=3)
                                              Buffers: shared hit=128 read=21833
                                        ->  Parallel Seq Scan on events_2018_02 e_1_2  (cost=0.00..24481.30 rows=482030 width=36) (actual time=0.448..160.483 rows=385624 loops=3)
                                              Buffers: shared hit=128 read=19533
                                        ->  Parallel Seq Scan on events_2018_03 e_1_3  (cost=0.00..27685.44 rows=544444 width=36) (actual time=0.076..167.997 rows=435555 loops=3)
                                              Buffers: shared hit=128 read=22113
                                        ->  Parallel Seq Scan on events_2018_04 e_1_4  (cost=0.00..24099.90 rows=473690 width=36) (actual time=0.080..146.110 rows=378952 loops=3)
                                              Buffers: shared hit=128 read=19235
                                        ->  Parallel Seq Scan on events_2018_05 e_1_5  (cost=0.00..24558.35 rows=484635 width=36) (actual time=0.095..153.181 rows=387708 loops=3)
                                              Buffers: shared hit=128 read=19584
                                        ->  Parallel Seq Scan on events_2018_06 e_1_6  (cost=0.00..26523.80 rows=525080 width=36) (actual time=0.077..175.118 rows=420064 loops=3)
                                              Buffers: shared hit=96 read=21177
                                        ->  Parallel Seq Scan on events_2018_07 e_1_7  (cost=0.00..25321.24 rows=502424 width=36) (actual time=0.099..158.335 rows=401939 loops=3)
                                              Buffers: shared hit=96 read=20201
                                        ->  Parallel Seq Scan on events_2018_08 e_1_8  (cost=0.00..27389.49 rows=542949 width=36) (actual time=0.116..174.982 rows=434359 loops=3)
                                              Buffers: shared hit=96 read=21864
                                        ->  Parallel Seq Scan on events_2018_09 e_1_9  (cost=0.00..26073.71 rows=516771 width=36) (actual time=0.103..166.982 rows=413417 loops=3)
                                              Buffers: shared hit=96 read=20810
                                        ->  Parallel Seq Scan on events_2018_10 e_1_10  (cost=0.00..27452.47 rows=545148 width=36) (actual time=0.071..175.492 rows=436118 loops=3)
                                              Buffers: shared hit=96 read=21905
                                        ->  Parallel Seq Scan on events_2018_11 e_1_11  (cost=0.00..27293.67 rows=535167 width=36) (actual time=0.072..174.818 rows=428133 loops=3)
                                              Buffers: shared hit=96 read=21846
                                        ->  Parallel Seq Scan on events_2018_12 e_1_12  (cost=0.00..27826.48 rows=540748 width=36) (actual time=0.108..177.344 rows=432599 loops=3)
                                              Buffers: shared hit=96 read=22323
                                        ->  Parallel Seq Scan on events_2019_01 e_1_13  (cost=0.00..28930.51 rows=572751 width=36) (actual time=0.112..183.352 rows=458201 loops=3)
                                              Buffers: shared hit=96 read=23107
                                        ->  Parallel Seq Scan on events_2019_02 e_1_14  (cost=0.00..27237.47 rows=542248 width=36) (actual time=0.072..174.700 rows=433798 loops=3)
                                              Buffers: shared hit=96 read=21719
                                        ->  Parallel Seq Scan on events_2019_03 e_1_15  (cost=0.00..29731.53 rows=591053 width=36) (actual time=0.106..192.812 rows=472843 loops=3)
                                              Buffers: shared hit=96 read=23725
                                        ->  Parallel Seq Scan on events_2019_04 e_1_16  (cost=0.00..27408.80 rows=545680 width=36) (actual time=0.075..170.280 rows=436544 loops=3)
                                              Buffers: shared hit=96 read=21856
                                        ->  Parallel Seq Scan on events_2019_05 e_1_17  (cost=0.00..29647.14 rows=590814 width=36) (actual time=0.094..186.307 rows=472651 loops=3)
                                              Buffers: shared hit=96 read=23643
                                        ->  Parallel Seq Scan on events_2019_06 e_1_18  (cost=0.00..27774.97 rows=551497 width=36) (actual time=0.118..178.236 rows=441197 loops=3)
                                              Buffers: shared hit=96 read=22164
                                        ->  Parallel Seq Scan on events_2019_07 e_1_19  (cost=0.00..29152.86 rows=580286 width=36) (actual time=0.097..184.164 rows=464229 loops=3)
                                              Buffers: shared hit=96 read=23254
                                        ->  Parallel Seq Scan on events_2019_08 e_1_20  (cost=0.00..28940.58 rows=576558 width=36) (actual time=0.072..193.012 rows=461247 loops=3)
                                              Buffers: shared hit=96 read=23079
                                        ->  Parallel Seq Scan on events_2019_09 e_1_21  (cost=0.00..28803.81 rows=574881 width=36) (actual time=0.075..185.001 rows=459905 loops=3)
                                              Buffers: shared hit=96 read=22959
                                        ->  Parallel Seq Scan on events_2019_10 e_1_22  (cost=0.00..31280.18 rows=622418 width=36) (actual time=0.072..197.873 rows=497934 loops=3)
                                              Buffers: shared hit=96 read=24960
                                        ->  Parallel Seq Scan on events_2019_11 e_1_23  (cost=0.00..34005.10 rows=673210 width=36) (actual time=0.084..211.443 rows=538568 loops=3)
                                              Buffers: shared hit=96 read=27177
                                        ->  Parallel Seq Scan on events_2019_12 e_1_24  (cost=0.00..34861.76 rows=688076 width=36) (actual time=0.074..228.706 rows=550461 loops=3)
                                              Buffers: shared hit=96 read=27885
                                        ->  Parallel Seq Scan on events_2020_01 e_1_25  (cost=0.00..32485.37 rows=644137 width=36) (actual time=0.071..202.208 rows=515309 loops=3)
                                              Buffers: shared hit=96 read=25948
                                        ->  Parallel Seq Scan on events_2020_02 e_1_26  (cost=0.00..31654.59 rows=627759 width=36) (actual time=0.075..188.000 rows=502207 loops=3)
                                              Buffers: shared hit=96 read=25281
                                        ->  Parallel Seq Scan on events_2020_03 e_1_27  (cost=0.00..20041.85 rows=396985 width=36) (actual time=0.135..122.292 rows=317588 loops=3)
                                              Buffers: shared hit=96 read=15976
                                        ->  Parallel Seq Scan on events_2020_04 e_1_28  (cost=0.00..10333.11 rows=205111 width=36) (actual time=0.075..66.141 rows=164089 loops=3)
                                              Buffers: shared hit=96 read=8186
                                        ->  Parallel Seq Scan on events_2020_05 e_1_29  (cost=0.00..19341.51 rows=383051 width=36) (actual time=0.074..129.225 rows=306441 loops=3)
                                              Buffers: shared hit=96 read=15415
                                        ->  Parallel Seq Scan on events_2020_06 e_1_30  (cost=0.00..24443.33 rows=483733 width=36) (actual time=0.078..162.250 rows=386987 loops=3)
                                              Buffers: shared hit=96 read=19510
                                        ->  Parallel Seq Scan on events_2020_07 e_1_31  (cost=0.00..28273.57 rows=561657 width=36) (actual time=0.077..172.975 rows=449326 loops=3)
                                              Buffers: shared hit=96 read=22561
                                        ->  Parallel Seq Scan on events_2020_08 e_1_32  (cost=0.00..28377.88 rows=563188 width=36) (actual time=0.107..185.493 rows=450550 loops=3)
                                              Buffers: shared hit=96 read=22650
                                        ->  Parallel Seq Scan on events_2020_09 e_1_33  (cost=0.00..29849.43 rows=592443 width=36) (actual time=0.116..180.517 rows=473954 loops=3)
                                              Buffers: shared hit=96 read=23829
                                        ->  Parallel Seq Scan on events_2020_10 e_1_34  (cost=0.00..30887.97 rows=613598 width=36) (actual time=0.083..197.719 rows=490878 loops=3)
                                              Buffers: shared hit=96 read=24656
                                        ->  Parallel Seq Scan on events_2020_11 e_1_35  (cost=0.00..24920.20 rows=495220 width=36) (actual time=0.106..160.860 rows=396176 loops=3)
                                              Buffers: shared hit=96 read=19872
                                        ->  Parallel Seq Scan on events_2020_12 e_1_36  (cost=0.00..20743.41 rows=413641 width=36) (actual time=0.128..128.392 rows=330913 loops=3)
                                              Buffers: shared hit=96 read=16511
                                        ->  Parallel Seq Scan on events_2021_01 e_1_37  (cost=0.00..13223.51 rows=262851 width=36) (actual time=0.070..88.114 rows=210281 loops=3)
                                              Buffers: shared hit=96 read=10499
                                        ->  Parallel Seq Scan on events_2021_02 e_1_38  (cost=0.00..13419.72 rows=266672 width=36) (actual time=0.075..82.828 rows=213338 loops=3)
                                              Buffers: shared hit=96 read=10657
                                        ->  Parallel Seq Scan on events_2021_03 e_1_39  (cost=0.00..20760.23 rows=417123 width=36) (actual time=0.069..132.068 rows=333698 loops=3)
                                              Buffers: shared hit=96 read=16493
                                        ->  Parallel Seq Scan on events_2021_04 e_1_40  (cost=0.00..18513.66 rows=371566 width=36) (actual time=0.088..115.614 rows=297253 loops=3)
                                              Buffers: shared hit=96 read=14702
                                        ->  Parallel Seq Scan on events_2021_05 e_1_41  (cost=0.00..22992.24 rows=461124 width=36) (actual time=0.078..156.359 rows=368899 loops=3)
                                              Buffers: shared hit=96 read=18285
                                        ->  Parallel Seq Scan on events_2021_06 e_1_42  (cost=0.00..32086.47 rows=653047 width=36) (actual time=0.106..211.789 rows=522438 loops=3)
                                              Buffers: shared hit=96 read=25460
                                        ->  Parallel Seq Scan on events_2021_07 e_1_43  (cost=0.00..35158.22 rows=717622 width=36) (actual time=0.107..224.169 rows=574097 loops=3)
                                              Buffers: shared hit=96 read=27886
                                        ->  Parallel Seq Scan on events_2021_08 e_1_44  (cost=0.00..36240.32 rows=741232 width=36) (actual time=0.076..236.441 rows=592985 loops=3)
                                              Buffers: shared hit=96 read=28732
                                        ->  Parallel Seq Scan on events_2021_09 e_1_45  (cost=0.00..36505.14 rows=748614 width=36) (actual time=0.065..230.759 rows=598893 loops=3)
                                              Buffers: shared hit=96 read=28923
                                        ->  Parallel Seq Scan on events_2021_10 e_1_46  (cost=0.00..43014.50 rows=894550 width=36) (actual time=0.085..276.106 rows=715643 loops=3)
                                              Buffers: shared hit=96 read=33973
                                        ->  Parallel Seq Scan on events_2021_11 e_1_47  (cost=0.00..40632.53 rows=841652 width=36) (actual time=0.077..258.176 rows=673459 loops=3)
                                              Buffers: shared hit=96 read=32120
                                        ->  Parallel Seq Scan on events_2021_12 e_1_48  (cost=0.00..42952.06 rows=894306 width=36) (actual time=0.100..268.463 rows=715962 loops=3)
                                              Buffers: shared hit=96 read=33913
                                        ->  Parallel Seq Scan on events_2022_01 e_1_49  (cost=0.00..36789.60 rows=765160 width=36) (actual time=0.072..239.944 rows=612128 loops=3)
                                              Buffers: shared hit=96 read=29042
                                        ->  Parallel Seq Scan on events_2022_02 e_1_50  (cost=0.00..37959.46 rows=794046 width=36) (actual time=0.078..263.473 rows=635949 loops=3)
                                              Buffers: shared hit=96 read=29923
                                        ->  Parallel Seq Scan on events_2022_03 e_1_51  (cost=0.00..1709.01 rows=46601 width=36) (actual time=0.024..8.813 rows=26407 loops=3)
                                              Buffers: shared hit=1243
  ->  Hash Left Join  (cost=142.62..1119690.02 rows=5775196 width=146) (actual time=85580.537..503667.299 rows=6734265 loops=1)
        Hash Cond: (e.device_type = ddt.typeid)
        Buffers: shared hit=2318 read=376956, temp read=424087 written=839346
        ->  Hash Left Join  (cost=140.55..1104206.82 rows=5775196 width=127) (actual time=85580.506..501171.677 rows=6734265 loops=1)
              Hash Cond: (e.ticket_type = dtt.typeid)
              Buffers: shared hit=2317 read=376956, temp read=424087 written=839346
              ->  Hash Join  (cost=138.24..1088723.38 rows=5775196 width=108) (actual time=85580.434..498639.758 rows=6734265 loops=1)
                    Hash Cond: (e.licenseplatekey = d.key)
                    Buffers: shared hit=2316 read=376956, temp read=424087 written=839346
                    ->  Hash Join  (cost=102.45..1073458.17 rows=5775196 width=71) (actual time=85579.980..490328.909 rows=67367271 loops=1)
                          Hash Cond: (e.carpark_id = df.facilityid)
                          Join Filter: ((e.date >= date(df.scd_start)) AND ((e.date < date(df.scd_end)) OR (df.scd_end IS NULL)))
                          Rows Removed by Join Filter: 648583247
                          Buffers: shared hit=2301 read=376956, temp read=424087 written=839346
                          ->  CTE Scan on e  (cost=0.00..135281.98 rows=6764099 width=40) (actual time=85578.915..298598.190 rows=67400480 loops=1)
                                Buffers: shared hit=2235 read=376956, temp read=424087 written=839346
                          ->  Hash  (cost=82.20..82.20 rows=1620 width=55) (actual time=1.026..1.027 rows=1639 loops=1)
                                Buckets: 2048  Batches: 1  Memory Usage: 161kB
                                Buffers: shared hit=66
                                ->  Seq Scan on dimfacility df  (cost=0.00..82.20 rows=1620 width=55) (actual time=0.026..0.648 rows=1639 loops=1)
                                      Buffers: shared hit=66
                    ->  Hash  (cost=24.24..24.24 rows=924 width=41) (actual time=0.430..0.431 rows=924 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 76kB
                          Buffers: shared hit=15
                          ->  Seq Scan on dim_licenseplate d  (cost=0.00..24.24 rows=924 width=41) (actual time=0.016..0.211 rows=924 loops=1)
                                Buffers: shared hit=15
              ->  Hash  (cost=1.95..1.95 rows=29 width=23) (actual time=0.061..0.062 rows=29 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 10kB
                    Buffers: shared hit=1
                    ->  Seq Scan on dimtype dtt  (cost=0.00..1.95 rows=29 width=23) (actual time=0.015..0.026 rows=29 loops=1)
                          Filter: ((mapping_type)::text = 'ticket_type'::text)
                          Rows Removed by Filter: 47
                          Buffers: shared hit=1
        ->  Hash  (cost=1.95..1.95 rows=9 width=23) (actual time=0.022..0.023 rows=9 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              Buffers: shared hit=1
              ->  Seq Scan on dimtype ddt  (cost=0.00..1.95 rows=9 width=23) (actual time=0.009..0.018 rows=9 loops=1)
                    Filter: ((mapping_type)::text = 'device_type'::text)
                    Rows Removed by Filter: 67
                    Buffers: shared hit=1
  ->  Hash  (cost=1.95..1.95 rows=9 width=23) (actual time=0.041..0.042 rows=9 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        Buffers: shared hit=1
        ->  Seq Scan on dimtype det  (cost=0.00..1.95 rows=9 width=23) (actual time=0.032..0.037 rows=9 loops=1)
              Filter: ((mapping_type)::text = 'event_type'::text)
              Rows Removed by Filter: 67
              Buffers: shared hit=1
Planning time: 4.618 ms
Execution time: 508178.331 ms

源表上的索引

  • 表名：事件（带有分区，例如 events_2021_01、events_2021_02 等）
  • 我也刚刚看到我有索引，直到仅2021_07，也许这可能是原因？
CREATE UNIQUE INDEX events_pkey1 ON public.events USING btree (id);

CREATE INDEX idx_duration_entry ON public.events USING btree (card_nr) WHERE ((device_type = 1) AND ((event_type = 2) OR (event_type = 12)) AND ((manufacturer)::text ~~ 'DESIGNA_ABACUS%'::text));

CREATE INDEX idx_duration_exit ON public.events USING btree (card_nr) WHERE ((device_type = 2) AND ((event_type = 2) OR (event_type = 12)) AND ((manufacturer)::text ~~ 'DESIGNA_ABACUS%'::text));

------------------------------------------------------
These indexes are repeated on all partition tables till 2021_07.

CREATE INDEX idx_carpark_events_2018_01 ON public.events_2018_01 USING btree (carpark_id, date_part('year'::text, event_time), date_part('week'::text, event_time), date_part('day'::text, event_time), event_type);

CREATE INDEX idx_events_2018_01 ON public.events_2018_01 USING btree (event_time);
    
CREATE INDEX idx_events_manufacturer_2018_01 ON public.events_2018_01 USING btree (event_time DESC, manufacturer);
    
CREATE INDEX idx_carpark_events_2018_02 ON public.events_2018_02 USING btree (carpark_id, date_part('year'::text, event_time), date_part('week'::text, event_time), date_part('day'::text, event_time), event_type);```


**Source Table DDL**


    CREATE TABLE public.events (
        id int8 NOT NULL,
        odb_created_at timestamp NOT NULL,
        event_time timestamp NOT NULL,
        device_type int4 NOT NULL,
        event_type int4 NOT NULL,
        ticket_type int4 NOT NULL,
        card_nr varchar(100) NULL,
        count int4 NOT NULL DEFAULT 1,
        manufacturer varchar(200) NULL,
        carpark_id int8 NULL,
        licenseplatekey int4 NULL,
        CONSTRAINT events_pkey1 PRIMARY KEY (id)
    );
    CREATE INDEX idx_duration_entry ON public.events USING btree (card_nr) WHERE ((device_type = 1) AND ((event_type = 2) OR (event_type = 12)) AND ((manufacturer)::text ~~ 'DESIGNA_ABACUS%'::text));
    CREATE INDEX idx_duration_exit ON public.events USING btree (card_nr) WHERE ((device_type = 2) AND ((event_type = 2) OR (event_type = 12)) AND ((manufacturer)::text ~~ 'DESIGNA_ABACUS%'::text));
    
    -- Table Triggers
    
    create trigger events_partition_trigger before
    insert
        on
        public.events for each row execute procedure events_partition_function();
原文

I have created a view that I use to generate some reports afterwards.
When first creating it, I used to have a small amount of data and couldn't notice any issues.

The view now takes forever to load and leads to a lot of performance issues.
I tried to limit the view this way:

WHERE date_part('year'::text, e.date) >= date_part('year'::text, CURRENT_DATE - '1 year'::interval);

It unfortunately did not help at all.
Is there an efficient way to speed up my query?

CREATE OR REPLACE VIEW public.v_lpr
AS WITH e AS (
         SELECT e_1.carpark_id,
            e_1.event_time AS date,
            e_1.device_type,
            e_1.event_type,
            e_1.ticket_type,
            sum(
                CASE
                    WHEN e_1.count > 0 THEN e_1.count
                    ELSE 1
                END) AS sum_event,
            e_1.licenseplatekey
           FROM events e_1
          GROUP BY e_1.carpark_id, e_1.event_type, e_1.device_type, e_1.ticket_type, e_1.event_time, e_1.licenseplatekey
        )
 SELECT e.carpark_id,
    e.date,
    e.device_type,
    COALESCE(ddt.description, 'Unknown'::character varying) AS device_type_name,
    e.event_type,
    COALESCE(det.description, 'Unknown'::character varying) AS event_type_name,
    e.ticket_type,
    COALESCE(dtt.description, 'Other'::character varying) AS ticket_type_name,
    e.sum_event AS count,
    e.licenseplatekey,
    d.country,
    d.state,
    d.district,
    df.facilityname,
    df.tenantname
   FROM e
     JOIN dim_licenseplate d ON d.key = e.licenseplatekey
     JOIN dimfacility df ON df.facilityid = e.carpark_id AND e.date >= date(df.scd_start) AND (e.date < date(df.scd_end) OR df.scd_end IS NULL)
     LEFT JOIN dimtype dtt ON dtt.typeid = e.ticket_type AND dtt.mapping_type::text = 'ticket_type'::text
     LEFT JOIN dimtype ddt ON ddt.typeid = e.device_type AND ddt.mapping_type::text = 'device_type'::text
     LEFT JOIN dimtype det ON det.typeid = e.event_type AND det.mapping_type::text = 'event_type'::text;

Query Plan (https://explain.dalibo.com/plan/lGl)

Hash Left Join  (cost=10060558.48..11195587.02 rows=5775196 width=204) (actual time=85580.589..507072.608 rows=6734265 loops=1)
  Hash Cond: (e.event_type = det.typeid)
  Buffers: shared hit=6373 read=1118955, temp read=1251698 written=1668112
  CTE e
    ->  Finalize GroupAggregate  (cost=7492458.49..10060413.80 rows=6764099 width=40) (actual time=85578.911..256822.632 rows=67400480 loops=1)
          Group Key: e_1.carpark_id, e_1.event_type, e_1.device_type, e_1.ticket_type, e_1.event_time, e_1.licenseplatekey
          Buffers: shared hit=6289 read=1118955, temp read=1251698 written=1253441
          ->  Gather Merge  (cost=7492458.49..9756029.35 rows=13528198 width=40) (actual time=85578.901..211729.452 rows=67431826 loops=1)
                Workers Planned: 2
                Workers Launched: 2
                Buffers: shared hit=6289 read=1118955, temp read=1251698 written=1253441
                ->  Partial GroupAggregate  (cost=7491458.47..8193539.61 rows=6764099 width=40) (actual time=84935.312..121437.041 rows=22477275 loops=3)
                      Group Key: e_1.carpark_id, e_1.event_type, e_1.device_type, e_1.ticket_type, e_1.event_time, e_1.licenseplatekey
                      Buffers: shared hit=6289 read=1118955, temp read=1251698 written=1253441
                      ->  Sort  (cost=7491458.47..7561951.82 rows=28197340 width=36) (actual time=84935.300..103478.557 rows=22548366 loops=3)
                            Sort Key: e_1.carpark_id, e_1.event_type, e_1.device_type, e_1.ticket_type, e_1.event_time, e_1.licenseplatekey
                            Sort Method: external merge  Disk: 947672kB
                            Buffers: shared hit=6289 read=1118955, temp read=1251698 written=1253441
                            ->  Result  (cost=0.00..1689104.77 rows=28197340 width=36) (actual time=0.066..15116.298 rows=22548366 loops=3)
                                  Buffers: shared hit=6203 read=1118955
                                  ->  Append  (cost=0.00..1407131.37 rows=28197340 width=36) (actual time=0.065..11417.413 rows=22548366 loops=3)
                                        Buffers: shared hit=6203 read=1118955
                                        ->  Parallel Seq Scan on events e_1  (cost=0.00..0.00 rows=1 width=36) (actual time=0.001..0.001 rows=0 loops=3)
                                        ->  Parallel Seq Scan on events_2018_01 e_1_1  (cost=0.00..27341.93 rows=538092 width=36) (actual time=0.063..187.542 rows=430474 loops=3)
                                              Buffers: shared hit=128 read=21833
                                        ->  Parallel Seq Scan on events_2018_02 e_1_2  (cost=0.00..24481.30 rows=482030 width=36) (actual time=0.448..160.483 rows=385624 loops=3)
                                              Buffers: shared hit=128 read=19533
                                        ->  Parallel Seq Scan on events_2018_03 e_1_3  (cost=0.00..27685.44 rows=544444 width=36) (actual time=0.076..167.997 rows=435555 loops=3)
                                              Buffers: shared hit=128 read=22113
                                        ->  Parallel Seq Scan on events_2018_04 e_1_4  (cost=0.00..24099.90 rows=473690 width=36) (actual time=0.080..146.110 rows=378952 loops=3)
                                              Buffers: shared hit=128 read=19235
                                        ->  Parallel Seq Scan on events_2018_05 e_1_5  (cost=0.00..24558.35 rows=484635 width=36) (actual time=0.095..153.181 rows=387708 loops=3)
                                              Buffers: shared hit=128 read=19584
                                        ->  Parallel Seq Scan on events_2018_06 e_1_6  (cost=0.00..26523.80 rows=525080 width=36) (actual time=0.077..175.118 rows=420064 loops=3)
                                              Buffers: shared hit=96 read=21177
                                        ->  Parallel Seq Scan on events_2018_07 e_1_7  (cost=0.00..25321.24 rows=502424 width=36) (actual time=0.099..158.335 rows=401939 loops=3)
                                              Buffers: shared hit=96 read=20201
                                        ->  Parallel Seq Scan on events_2018_08 e_1_8  (cost=0.00..27389.49 rows=542949 width=36) (actual time=0.116..174.982 rows=434359 loops=3)
                                              Buffers: shared hit=96 read=21864
                                        ->  Parallel Seq Scan on events_2018_09 e_1_9  (cost=0.00..26073.71 rows=516771 width=36) (actual time=0.103..166.982 rows=413417 loops=3)
                                              Buffers: shared hit=96 read=20810
                                        ->  Parallel Seq Scan on events_2018_10 e_1_10  (cost=0.00..27452.47 rows=545148 width=36) (actual time=0.071..175.492 rows=436118 loops=3)
                                              Buffers: shared hit=96 read=21905
                                        ->  Parallel Seq Scan on events_2018_11 e_1_11  (cost=0.00..27293.67 rows=535167 width=36) (actual time=0.072..174.818 rows=428133 loops=3)
                                              Buffers: shared hit=96 read=21846
                                        ->  Parallel Seq Scan on events_2018_12 e_1_12  (cost=0.00..27826.48 rows=540748 width=36) (actual time=0.108..177.344 rows=432599 loops=3)
                                              Buffers: shared hit=96 read=22323
                                        ->  Parallel Seq Scan on events_2019_01 e_1_13  (cost=0.00..28930.51 rows=572751 width=36) (actual time=0.112..183.352 rows=458201 loops=3)
                                              Buffers: shared hit=96 read=23107
                                        ->  Parallel Seq Scan on events_2019_02 e_1_14  (cost=0.00..27237.47 rows=542248 width=36) (actual time=0.072..174.700 rows=433798 loops=3)
                                              Buffers: shared hit=96 read=21719
                                        ->  Parallel Seq Scan on events_2019_03 e_1_15  (cost=0.00..29731.53 rows=591053 width=36) (actual time=0.106..192.812 rows=472843 loops=3)
                                              Buffers: shared hit=96 read=23725
                                        ->  Parallel Seq Scan on events_2019_04 e_1_16  (cost=0.00..27408.80 rows=545680 width=36) (actual time=0.075..170.280 rows=436544 loops=3)
                                              Buffers: shared hit=96 read=21856
                                        ->  Parallel Seq Scan on events_2019_05 e_1_17  (cost=0.00..29647.14 rows=590814 width=36) (actual time=0.094..186.307 rows=472651 loops=3)
                                              Buffers: shared hit=96 read=23643
                                        ->  Parallel Seq Scan on events_2019_06 e_1_18  (cost=0.00..27774.97 rows=551497 width=36) (actual time=0.118..178.236 rows=441197 loops=3)
                                              Buffers: shared hit=96 read=22164
                                        ->  Parallel Seq Scan on events_2019_07 e_1_19  (cost=0.00..29152.86 rows=580286 width=36) (actual time=0.097..184.164 rows=464229 loops=3)
                                              Buffers: shared hit=96 read=23254
                                        ->  Parallel Seq Scan on events_2019_08 e_1_20  (cost=0.00..28940.58 rows=576558 width=36) (actual time=0.072..193.012 rows=461247 loops=3)
                                              Buffers: shared hit=96 read=23079
                                        ->  Parallel Seq Scan on events_2019_09 e_1_21  (cost=0.00..28803.81 rows=574881 width=36) (actual time=0.075..185.001 rows=459905 loops=3)
                                              Buffers: shared hit=96 read=22959
                                        ->  Parallel Seq Scan on events_2019_10 e_1_22  (cost=0.00..31280.18 rows=622418 width=36) (actual time=0.072..197.873 rows=497934 loops=3)
                                              Buffers: shared hit=96 read=24960
                                        ->  Parallel Seq Scan on events_2019_11 e_1_23  (cost=0.00..34005.10 rows=673210 width=36) (actual time=0.084..211.443 rows=538568 loops=3)
                                              Buffers: shared hit=96 read=27177
                                        ->  Parallel Seq Scan on events_2019_12 e_1_24  (cost=0.00..34861.76 rows=688076 width=36) (actual time=0.074..228.706 rows=550461 loops=3)
                                              Buffers: shared hit=96 read=27885
                                        ->  Parallel Seq Scan on events_2020_01 e_1_25  (cost=0.00..32485.37 rows=644137 width=36) (actual time=0.071..202.208 rows=515309 loops=3)
                                              Buffers: shared hit=96 read=25948
                                        ->  Parallel Seq Scan on events_2020_02 e_1_26  (cost=0.00..31654.59 rows=627759 width=36) (actual time=0.075..188.000 rows=502207 loops=3)
                                              Buffers: shared hit=96 read=25281
                                        ->  Parallel Seq Scan on events_2020_03 e_1_27  (cost=0.00..20041.85 rows=396985 width=36) (actual time=0.135..122.292 rows=317588 loops=3)
                                              Buffers: shared hit=96 read=15976
                                        ->  Parallel Seq Scan on events_2020_04 e_1_28  (cost=0.00..10333.11 rows=205111 width=36) (actual time=0.075..66.141 rows=164089 loops=3)
                                              Buffers: shared hit=96 read=8186
                                        ->  Parallel Seq Scan on events_2020_05 e_1_29  (cost=0.00..19341.51 rows=383051 width=36) (actual time=0.074..129.225 rows=306441 loops=3)
                                              Buffers: shared hit=96 read=15415
                                        ->  Parallel Seq Scan on events_2020_06 e_1_30  (cost=0.00..24443.33 rows=483733 width=36) (actual time=0.078..162.250 rows=386987 loops=3)
                                              Buffers: shared hit=96 read=19510
                                        ->  Parallel Seq Scan on events_2020_07 e_1_31  (cost=0.00..28273.57 rows=561657 width=36) (actual time=0.077..172.975 rows=449326 loops=3)
                                              Buffers: shared hit=96 read=22561
                                        ->  Parallel Seq Scan on events_2020_08 e_1_32  (cost=0.00..28377.88 rows=563188 width=36) (actual time=0.107..185.493 rows=450550 loops=3)
                                              Buffers: shared hit=96 read=22650
                                        ->  Parallel Seq Scan on events_2020_09 e_1_33  (cost=0.00..29849.43 rows=592443 width=36) (actual time=0.116..180.517 rows=473954 loops=3)
                                              Buffers: shared hit=96 read=23829
                                        ->  Parallel Seq Scan on events_2020_10 e_1_34  (cost=0.00..30887.97 rows=613598 width=36) (actual time=0.083..197.719 rows=490878 loops=3)
                                              Buffers: shared hit=96 read=24656
                                        ->  Parallel Seq Scan on events_2020_11 e_1_35  (cost=0.00..24920.20 rows=495220 width=36) (actual time=0.106..160.860 rows=396176 loops=3)
                                              Buffers: shared hit=96 read=19872
                                        ->  Parallel Seq Scan on events_2020_12 e_1_36  (cost=0.00..20743.41 rows=413641 width=36) (actual time=0.128..128.392 rows=330913 loops=3)
                                              Buffers: shared hit=96 read=16511
                                        ->  Parallel Seq Scan on events_2021_01 e_1_37  (cost=0.00..13223.51 rows=262851 width=36) (actual time=0.070..88.114 rows=210281 loops=3)
                                              Buffers: shared hit=96 read=10499
                                        ->  Parallel Seq Scan on events_2021_02 e_1_38  (cost=0.00..13419.72 rows=266672 width=36) (actual time=0.075..82.828 rows=213338 loops=3)
                                              Buffers: shared hit=96 read=10657
                                        ->  Parallel Seq Scan on events_2021_03 e_1_39  (cost=0.00..20760.23 rows=417123 width=36) (actual time=0.069..132.068 rows=333698 loops=3)
                                              Buffers: shared hit=96 read=16493
                                        ->  Parallel Seq Scan on events_2021_04 e_1_40  (cost=0.00..18513.66 rows=371566 width=36) (actual time=0.088..115.614 rows=297253 loops=3)
                                              Buffers: shared hit=96 read=14702
                                        ->  Parallel Seq Scan on events_2021_05 e_1_41  (cost=0.00..22992.24 rows=461124 width=36) (actual time=0.078..156.359 rows=368899 loops=3)
                                              Buffers: shared hit=96 read=18285
                                        ->  Parallel Seq Scan on events_2021_06 e_1_42  (cost=0.00..32086.47 rows=653047 width=36) (actual time=0.106..211.789 rows=522438 loops=3)
                                              Buffers: shared hit=96 read=25460
                                        ->  Parallel Seq Scan on events_2021_07 e_1_43  (cost=0.00..35158.22 rows=717622 width=36) (actual time=0.107..224.169 rows=574097 loops=3)
                                              Buffers: shared hit=96 read=27886
                                        ->  Parallel Seq Scan on events_2021_08 e_1_44  (cost=0.00..36240.32 rows=741232 width=36) (actual time=0.076..236.441 rows=592985 loops=3)
                                              Buffers: shared hit=96 read=28732
                                        ->  Parallel Seq Scan on events_2021_09 e_1_45  (cost=0.00..36505.14 rows=748614 width=36) (actual time=0.065..230.759 rows=598893 loops=3)
                                              Buffers: shared hit=96 read=28923
                                        ->  Parallel Seq Scan on events_2021_10 e_1_46  (cost=0.00..43014.50 rows=894550 width=36) (actual time=0.085..276.106 rows=715643 loops=3)
                                              Buffers: shared hit=96 read=33973
                                        ->  Parallel Seq Scan on events_2021_11 e_1_47  (cost=0.00..40632.53 rows=841652 width=36) (actual time=0.077..258.176 rows=673459 loops=3)
                                              Buffers: shared hit=96 read=32120
                                        ->  Parallel Seq Scan on events_2021_12 e_1_48  (cost=0.00..42952.06 rows=894306 width=36) (actual time=0.100..268.463 rows=715962 loops=3)
                                              Buffers: shared hit=96 read=33913
                                        ->  Parallel Seq Scan on events_2022_01 e_1_49  (cost=0.00..36789.60 rows=765160 width=36) (actual time=0.072..239.944 rows=612128 loops=3)
                                              Buffers: shared hit=96 read=29042
                                        ->  Parallel Seq Scan on events_2022_02 e_1_50  (cost=0.00..37959.46 rows=794046 width=36) (actual time=0.078..263.473 rows=635949 loops=3)
                                              Buffers: shared hit=96 read=29923
                                        ->  Parallel Seq Scan on events_2022_03 e_1_51  (cost=0.00..1709.01 rows=46601 width=36) (actual time=0.024..8.813 rows=26407 loops=3)
                                              Buffers: shared hit=1243
  ->  Hash Left Join  (cost=142.62..1119690.02 rows=5775196 width=146) (actual time=85580.537..503667.299 rows=6734265 loops=1)
        Hash Cond: (e.device_type = ddt.typeid)
        Buffers: shared hit=2318 read=376956, temp read=424087 written=839346
        ->  Hash Left Join  (cost=140.55..1104206.82 rows=5775196 width=127) (actual time=85580.506..501171.677 rows=6734265 loops=1)
              Hash Cond: (e.ticket_type = dtt.typeid)
              Buffers: shared hit=2317 read=376956, temp read=424087 written=839346
              ->  Hash Join  (cost=138.24..1088723.38 rows=5775196 width=108) (actual time=85580.434..498639.758 rows=6734265 loops=1)
                    Hash Cond: (e.licenseplatekey = d.key)
                    Buffers: shared hit=2316 read=376956, temp read=424087 written=839346
                    ->  Hash Join  (cost=102.45..1073458.17 rows=5775196 width=71) (actual time=85579.980..490328.909 rows=67367271 loops=1)
                          Hash Cond: (e.carpark_id = df.facilityid)
                          Join Filter: ((e.date >= date(df.scd_start)) AND ((e.date < date(df.scd_end)) OR (df.scd_end IS NULL)))
                          Rows Removed by Join Filter: 648583247
                          Buffers: shared hit=2301 read=376956, temp read=424087 written=839346
                          ->  CTE Scan on e  (cost=0.00..135281.98 rows=6764099 width=40) (actual time=85578.915..298598.190 rows=67400480 loops=1)
                                Buffers: shared hit=2235 read=376956, temp read=424087 written=839346
                          ->  Hash  (cost=82.20..82.20 rows=1620 width=55) (actual time=1.026..1.027 rows=1639 loops=1)
                                Buckets: 2048  Batches: 1  Memory Usage: 161kB
                                Buffers: shared hit=66
                                ->  Seq Scan on dimfacility df  (cost=0.00..82.20 rows=1620 width=55) (actual time=0.026..0.648 rows=1639 loops=1)
                                      Buffers: shared hit=66
                    ->  Hash  (cost=24.24..24.24 rows=924 width=41) (actual time=0.430..0.431 rows=924 loops=1)
                          Buckets: 1024  Batches: 1  Memory Usage: 76kB
                          Buffers: shared hit=15
                          ->  Seq Scan on dim_licenseplate d  (cost=0.00..24.24 rows=924 width=41) (actual time=0.016..0.211 rows=924 loops=1)
                                Buffers: shared hit=15
              ->  Hash  (cost=1.95..1.95 rows=29 width=23) (actual time=0.061..0.062 rows=29 loops=1)
                    Buckets: 1024  Batches: 1  Memory Usage: 10kB
                    Buffers: shared hit=1
                    ->  Seq Scan on dimtype dtt  (cost=0.00..1.95 rows=29 width=23) (actual time=0.015..0.026 rows=29 loops=1)
                          Filter: ((mapping_type)::text = 'ticket_type'::text)
                          Rows Removed by Filter: 47
                          Buffers: shared hit=1
        ->  Hash  (cost=1.95..1.95 rows=9 width=23) (actual time=0.022..0.023 rows=9 loops=1)
              Buckets: 1024  Batches: 1  Memory Usage: 9kB
              Buffers: shared hit=1
              ->  Seq Scan on dimtype ddt  (cost=0.00..1.95 rows=9 width=23) (actual time=0.009..0.018 rows=9 loops=1)
                    Filter: ((mapping_type)::text = 'device_type'::text)
                    Rows Removed by Filter: 67
                    Buffers: shared hit=1
  ->  Hash  (cost=1.95..1.95 rows=9 width=23) (actual time=0.041..0.042 rows=9 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        Buffers: shared hit=1
        ->  Seq Scan on dimtype det  (cost=0.00..1.95 rows=9 width=23) (actual time=0.032..0.037 rows=9 loops=1)
              Filter: ((mapping_type)::text = 'event_type'::text)
              Rows Removed by Filter: 67
              Buffers: shared hit=1
Planning time: 4.618 ms
Execution time: 508178.331 ms

Indexes on source table

  • Tablename: Events (with partitions e.g. events_2021_01, events_2021_02, etc)
  • I also just saw that I have indexes till 2021_07 only, maybe it could be the cause?
CREATE UNIQUE INDEX events_pkey1 ON public.events USING btree (id);

CREATE INDEX idx_duration_entry ON public.events USING btree (card_nr) WHERE ((device_type = 1) AND ((event_type = 2) OR (event_type = 12)) AND ((manufacturer)::text ~~ 'DESIGNA_ABACUS%'::text));

CREATE INDEX idx_duration_exit ON public.events USING btree (card_nr) WHERE ((device_type = 2) AND ((event_type = 2) OR (event_type = 12)) AND ((manufacturer)::text ~~ 'DESIGNA_ABACUS%'::text));

------------------------------------------------------
These indexes are repeated on all partition tables till 2021_07.

CREATE INDEX idx_carpark_events_2018_01 ON public.events_2018_01 USING btree (carpark_id, date_part('year'::text, event_time), date_part('week'::text, event_time), date_part('day'::text, event_time), event_type);

CREATE INDEX idx_events_2018_01 ON public.events_2018_01 USING btree (event_time);
    
CREATE INDEX idx_events_manufacturer_2018_01 ON public.events_2018_01 USING btree (event_time DESC, manufacturer);
    
CREATE INDEX idx_carpark_events_2018_02 ON public.events_2018_02 USING btree (carpark_id, date_part('year'::text, event_time), date_part('week'::text, event_time), date_part('day'::text, event_time), event_type);```


**Source Table DDL**


    CREATE TABLE public.events (
        id int8 NOT NULL,
        odb_created_at timestamp NOT NULL,
        event_time timestamp NOT NULL,
        device_type int4 NOT NULL,
        event_type int4 NOT NULL,
        ticket_type int4 NOT NULL,
        card_nr varchar(100) NULL,
        count int4 NOT NULL DEFAULT 1,
        manufacturer varchar(200) NULL,
        carpark_id int8 NULL,
        licenseplatekey int4 NULL,
        CONSTRAINT events_pkey1 PRIMARY KEY (id)
    );
    CREATE INDEX idx_duration_entry ON public.events USING btree (card_nr) WHERE ((device_type = 1) AND ((event_type = 2) OR (event_type = 12)) AND ((manufacturer)::text ~~ 'DESIGNA_ABACUS%'::text));
    CREATE INDEX idx_duration_exit ON public.events USING btree (card_nr) WHERE ((device_type = 2) AND ((event_type = 2) OR (event_type = 12)) AND ((manufacturer)::text ~~ 'DESIGNA_ABACUS%'::text));
    
    -- Table Triggers
    
    create trigger events_partition_trigger before
    insert
        on
        public.events for each row execute procedure events_partition_function();

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

呆橘 2025-01-18 09:46:56

条件 date_part('year'::text, e.date) ... 无法使用 date 列上的索引。

如果您只需要当前年份的行，请使用：

WHERE e.date >= date_trunc('year', CURRENT_DATE);

date_trunc('year', CURRENT_DATE) 返回“今年”的开始时间（1 月 1 日）

如果您需要今年和去年的行您可以使用：

WHERE e.date >= date_trunc('year', CURRENT_DATE) - interval '1 year';

连接中的 OR 条件也可能是性能杀手。

您可以避免这种情况下的 OR：

e.date >= date(df.scd_start) AND (e.date < date(df.scd_end) OR df.scd_end IS NULL)

通过使用自动将上端的 null 视为“无界”的日期范围

e.date <@ daterange(df.scd_start::date, df.scd_end::date)

并可能在该日期范围上创建索引：

create index on dimfacility 
   using gist (daterange(scd_start::date, scd_end::date))

If e.date 不是 date 而是 timestamp 并且 scd_start 和 scd_end 也是时间戳，然后使用 <代码>tsrange：

e.date <@ tsrange(df.scd_start, df.scd_end)

create index on dimfacility 
   using gist (tsrange(scd_start, scd_end))

The condition date_part('year'::text, e.date) ... can't make use of an index on the date column.

If you only want rows from the current year, use:

WHERE e.date >= date_trunc('year', CURRENT_DATE);

date_trunc('year', CURRENT_DATE) returns the start of "this year" (January, 1st)

If you want rows from this year and last year you can use:

WHERE e.date >= date_trunc('year', CURRENT_DATE) - interval '1 year';

The OR condition in the join is also likely a performance killer.

You can avoid the OR in this condition:

e.date >= date(df.scd_start) AND (e.date < date(df.scd_end) OR df.scd_end IS NULL)

by using a daterange that automatically treats null in the upper end as "unbounded"

e.date <@ daterange(df.scd_start::date, df.scd_end::date)

And possibly create an index on that daterange:

create index on dimfacility 
   using gist (daterange(scd_start::date, scd_end::date))

If e.date is not a date but a timestamp and the scd_start and scd_end are also timestamps, then use a tsrange:

e.date <@ tsrange(df.scd_start, df.scd_end)

create index on dimfacility 
   using gist (tsrange(scd_start, scd_end))
回复 原文
强者自强 2025-01-18 09:46:56

由于您修改了列中的值，因此您的 WHERE 子句不可控制。

WHERE date_part('year'::text, e.date) ...

所以不能使用索引。

将 WHERE 子句重写为可控制的，如下所示：

WHERE e.date >= CAST(CAST(date_part('year', CURRENT_DATE) -1 AS CHAR(4) ||'-01-01' AS DATE)

Your WHERE clause is not sargable due to the fact that you modify the value in the column.

WHERE date_part('year'::text, e.date) ...

So no index can be used.

Rewrite your WHERE clause to be sargable like this :

WHERE e.date >= CAST(CAST(date_part('year', CURRENT_DATE) -1 AS CHAR(4) ||'-01-01' AS DATE)
回复 原文
~没有更多了~

关于作者

伊面

暂无简介

文章
评论
25 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

卷耳

文章 0 评论 0

佚名

文章 0 评论 0

℉服软

文章 0 评论 0

qq_2gSKZM

文章 0 评论 0

凉宸

文章 0 评论 0

gyhjy

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文