Postgres SQL查询有时需要超过一分钟才能从大表中加载数据

Question

我有一个带有相当大的事件表的Postgres数据库，该表的平均查询时间在两个日期之间查询数据时 1秒，但有时相同的查询需要超过 1分钟完成执行。

表尺寸约为2M记录。

在下面附上我的表DDL：

CREATE TABLE public.events (
    id bigserial NOT NULL,
    "eventId" int4 NOT NULL,
    severity varchar(255) NOT NULL,
    "eventSrc" uuid NULL,
    "eventSrcType" varchar(255) NULL,
    "eventContext" json NOT NULL DEFAULT '{}'::json,
    "data" jsonb NOT NULL,
    "eventTimestamp" timestamptz NULL,
    "createdAt" timestamptz NOT NULL,
    "updatedAt" timestamptz NOT NULL,
    "deletedAt" timestamptz NULL,
    "organizationId" uuid NULL,
    "hierarchyId" uuid NULL,
    "eventUser" uuid NULL,
    CONSTRAINT events_pkey PRIMARY KEY (id),
    CONSTRAINT "events_eventId_eventIds_fk" FOREIGN KEY ("eventId") REFERENCES public."eventIds"("eventId") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "events_eventSrcType_eventSourceTypes_fk" FOREIGN KEY ("eventSrcType") REFERENCES public."eventSourceTypes"("srcType") ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "events_eventUser_fkey" FOREIGN KEY ("eventUser") REFERENCES public.users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT "events_hierarchyId_fkey" FOREIGN KEY ("hierarchyId") REFERENCES public.hierarchies(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT "events_organizationId_fkey" FOREIGN KEY ("organizationId") REFERENCES public.hierarchies(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX events_organizationid_idx ON public.events USING btree ("organizationId", "eventTimestamp", "eventId");

更新问题

查询如下：

select
    "events"."id",
    "events"."eventId",
    "events"."severity",
    "events"."eventSrc",
    "events"."eventSrcType",
    "events"."eventContext",
    "events"."data",
    "events"."eventUser",
    "events"."eventTimestamp",
    "events"."isAlert",
    "events"."isAudit",
    "events"."isActive",
    "events"."isArchived",
    "events"."organizationId",
    "events"."hierarchyId",
    "events"."createdAt",
    "events"."updatedAt",
    "events"."deletedAt",
    "device"."id" as "device.id",
    "device"."info" as "device.info",
    "device"."hierarchyId" as "device.hierarchyId",
    "device"."organizationId" as "device.organizationId",
    "device"."serialNo" as "device.serialNo",
    "device"."createdAt" as "device.createdAt",
    "device"."updatedAt" as "device.updatedAt",
    "device"."deletedAt" as "device.deletedAt",
    "eventSource"."id" as "eventSource.id",
    "eventSource"."sourceId" as "eventSource.sourceId",
    "eventSource"."sourceType" as "eventSource.sourceType",
    "eventSource"."name" as "eventSource.name",
    "eventSource"."createdAt" as "eventSource.createdAt",
    "eventSource"."updatedAt" as "eventSource.updatedAt",
    "eventSource"."deletedAt" as "eventSource.deletedAt",
    "user"."id" as "user.id",
    "user"."title" as "user.title",
    "user"."firstName" as "user.firstName",
    "user"."middleName" as "user.middleName",
    "user"."lastName" as "user.lastName",
    "user"."email" as "user.email",
    "user"."organizationId" as "user.organizationId",
    "user"."createdAt" as "user.createdAt",
    "user"."updatedAt" as "user.updatedAt",
    "user"."deletedAt" as "user.deletedAt",
    "eventConfig"."id" as "eventConfig.id",
    "eventConfig"."eventId" as "eventConfig.eventId",
    "eventConfig"."name" as "eventConfig.name",
    "eventConfig"."description" as "eventConfig.description",
    "eventConfig"."severity" as "eventConfig.severity",
    "eventConfig"."sourceType" as "eventConfig.sourceType",
    "eventConfig"."isDisabled" as "eventConfig.isDisabled",
    "eventConfig"."category" as "eventConfig.category",
    "eventConfig"."content" as "eventConfig.content",
    "eventConfig"."clearingEvent" as "eventConfig.clearingEvent",
    "eventConfig"."createdAt" as "eventConfig.createdAt",
    "eventConfig"."updatedAt" as "eventConfig.updatedAt",
    "eventConfig"."deletedAt" as "eventConfig.deletedAt",
    "organisation"."id" as "organisation.id",
    "organisation"."shortName" as "organisation.shortName",
    "hierarchy"."id" as "hierarchy.id",
    "hierarchy"."shortName" as "hierarchy.shortName"
from
    "events" as "events"
left outer join "devices" as "device" on
    "events"."eventSrc" = "device"."id"
    and ("device"."deletedAt" > '2022-05-24 09:02:33.651 +00:00'
        or "device"."deletedAt" is null)
left outer join "eventSources" as "eventSource" on
    "events"."eventSrc" = "eventSource"."id"
    and ("eventSource"."deletedAt" > '2022-05-24 09:02:33.651 +00:00'
        or "eventSource"."deletedAt" is null)
left outer join "users" as "user" on
    "events"."eventSrc" = "user"."id"
    and ("user"."deletedAt" > '2022-05-24 09:02:33.651 +00:00'
        or "user"."deletedAt" is null)
left outer join "eventIds" as "eventConfig" on
    "events"."eventId" = "eventConfig"."eventId"
    and ("eventConfig"."deletedAt" > '2022-05-24 09:02:33.651 +00:00'
        or "eventConfig"."deletedAt" is null)
left outer join "hierarchies" as "organisation" on
    "events"."organizationId" = "organisation"."id"
    and ("organisation"."deletedAt" > '2022-05-24 09:02:33.651 +00:00'
        or "organisation"."deletedAt" is null)
left outer join "hierarchies" as "hierarchy" on
    "events"."hierarchyId" = "hierarchy"."id"
    and ("hierarchy"."deletedAt" > '2022-05-24 09:02:33.651 +00:00'
        or "hierarchy"."deletedAt" is null)
where
    (("events"."deletedAt" > '2022-05-24 09:02:33.651 +00:00'
        or "events"."deletedAt" is null)
    and ("events"."eventTimestamp" between '2022-05-20 09:02:33.651 +00:00' and '2022-05-24 09:02:33.651 +00:00'
        and "events"."isAudit" = false))
order by
    "events"."eventTimestamp" desc
limit 1000 offset 0;

查询计划已使用（分析，缓冲区）说明：

Limit  (cost=63638.49..63638.50 rows=1 width=2187) (actual time=3426.527..3426.738 rows=1000 loops=1)
  Buffers: shared hit=1573900 read=21
  I/O Timings: read=169.648
  ->  Sort  (cost=63638.49..63638.50 rows=1 width=2187) (actual time=3426.526..3426.626 rows=1000 loops=1)
        Sort Key: events."eventTimestamp" DESC
        Sort Method: top-N heapsort  Memory: 3240kB
        Buffers: shared hit=1573900 read=21
        I/O Timings: read=169.648
        ->  Nested Loop Left Join  (cost=0.43..63638.48 rows=1 width=2187) (actual time=0.234..3393.467 rows=21983 loops=1)
              Join Filter: (events."hierarchyId" = hierarchy.id)
              Rows Removed by Join Filter: 1596748
              Buffers: shared hit=1573900 read=21
              I/O Timings: read=169.648
              ->  Nested Loop Left Join  (cost=0.43..63581.43 rows=1 width=2161) (actual time=0.192..2625.353 rows=21983 loops=1)
                    Join Filter: (events."organizationId" = organisation.id)
                    Rows Removed by Join Filter: 1776812
                    Buffers: shared hit=1372822 read=21
                    I/O Timings: read=169.648
                    ->  Nested Loop Left Join  (cost=0.43..63524.39 rows=1 width=2135) (actual time=0.168..1787.853 rows=21983 loops=1)
                          Join Filter: (events."eventId" = "eventConfig"."eventId")
                          Rows Removed by Join Filter: 1231462
                          Buffers: shared hit=1145413 read=21
                          I/O Timings: read=169.648
                          ->  Nested Loop Left Join  (cost=0.43..63516.20 rows=1 width=1997) (actual time=0.142..1353.144 rows=21983 loops=1)
                                Join Filter: (events."eventSrc" = "user".id)
                                Rows Removed by Join Filter: 1033201
                                Buffers: shared hit=1105304 read=21
                                I/O Timings: read=169.648
                                ->  Nested Loop Left Join  (cost=0.43..63470.54 rows=1 width=1903) (actual time=0.085..663.925 rows=21983 loops=1)
                                      Join Filter: (events."eventSrc" = "eventSource".id)
                                      Buffers: shared hit=138052 read=21
                                      I/O Timings: read=169.648
                                      ->  Nested Loop Left Join  (cost=0.43..63458.36 rows=1 width=1331) (actual time=0.082..637.683 rows=21983 loops=1)
                                            Join Filter: (events."eventSrc" = device.id)
                                            Rows Removed by Join Filter: 934793
                                            Buffers: shared hit=138052 read=21
                                            I/O Timings: read=169.648
                                            ->  Index Scan using events_organizationid_idx on events  (cost=0.43..63446.16 rows=1 width=1087) (actual time=0.057..243.683 rows=21983 loops=1)
                                                  Index Cond: (("eventTimestamp" >= '2022-05-20 14:32:33.651+05:30'::timestamp with time zone) AND ("eventTimestamp" <= '2022-05-24 14:32:33.651+05:30'::timestamp with time zone))
                                                  Filter: ((NOT "isAudit") AND (("deletedAt" > '2022-05-24 14:32:33.651+05:30'::timestamp with time zone) OR ("deletedAt" IS NULL)))
                                                  Rows Removed by Filter: 74
                                                  Buffers: shared hit=27000 read=21
                                                  I/O Timings: read=169.648
                                            ->  Seq Scan on devices device  (cost=0.00..11.30 rows=72 width=244) (actual time=0.001..0.012 rows=44 loops=21983)
                                                  Filter: (("deletedAt" > '2022-05-24 14:32:33.651+05:30'::timestamp with time zone) OR ("deletedAt" IS NULL))
                                                  Rows Removed by Filter: 31
                                                  Buffers: shared hit=111052
                                      ->  Seq Scan on "eventSources" "eventSource"  (cost=0.00..11.62 rows=44 width=572) (actual time=0.000..0.000 rows=0 loops=21983)
                                            Filter: (("deletedAt" > '2022-05-24 14:32:33.651+05:30'::timestamp with time zone) OR ("deletedAt" IS NULL))
                                ->  Seq Scan on users "user"  (cost=0.00..45.08 rows=47 width=94) (actual time=0.001..0.024 rows=47 loops=21983)
                                      Filter: (("deletedAt" > '2022-05-24 14:32:33.651+05:30'::timestamp with time zone) OR ("deletedAt" IS NULL))
                                      Rows Removed by Filter: 39
                                      Buffers: shared hit=967252
                          ->  Seq Scan on "eventIds" "eventConfig"  (cost=0.00..6.10 rows=167 width=138) (actual time=0.001..0.013 rows=57 loops=21983)
                                Filter: (("deletedAt" > '2022-05-24 14:32:33.651+05:30'::timestamp with time zone) OR ("deletedAt" IS NULL))
                                Rows Removed by Filter: 1
                                Buffers: shared hit=40109
                    ->  Seq Scan on hierarchies organisation  (cost=0.00..55.10 rows=156 width=26) (actual time=0.001..0.028 rows=82 loops=21983)
                          Filter: (("deletedAt" > '2022-05-24 14:32:33.651+05:30'::timestamp with time zone) OR ("deletedAt" IS NULL))
                          Rows Removed by Filter: 92
                          Buffers: shared hit=227409
              ->  Seq Scan on hierarchies hierarchy  (cost=0.00..55.10 rows=156 width=26) (actual time=0.001..0.026 rows=74 loops=21983)
                    Filter: (("deletedAt" > '2022-05-24 14:32:33.651+05:30'::timestamp with time zone) OR ("deletedAt" IS NULL))
                    Rows Removed by Filter: 90
                    Buffers: shared hit=201078
Planning Time: 1.303 ms
Execution Time: 3426.872 ms

我已经更新了查询计划。您可以在5天的日期范围内看到它的时间更多3秒。

Answer 1

PostgreSQL的写作很快，但用于阅读速度较慢，以提高查询的速度，建议在表格上创建更多索引。这可以大大提高选定查询的速度。

该链接可以解释如何比我更好地创建索引：

我希望这会有所帮助。