首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >Postgres SQL查询有时需要超过一分钟才能从大型表加载数据。

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

Stack Overflow用户
提问于 2022-05-18 08:49:00
回答 1查看 542关注 0票数 1

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

表大小约为200万条记录。

将我的表DDL附在下面:

代码语言:javascript
复制
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");

更新问题

查询如下:

代码语言:javascript
复制
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;

在这里用(分析、缓冲区)解释的查询:

代码语言:javascript
复制
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

我已经更新了查询plan.As,您现在可以看到的日期范围为5天,它需要超过3秒。

EN

回答 1

Stack Overflow用户

发布于 2022-05-18 13:07:48

Postgresql编写速度快,但读取速度慢,为了提高查询的速度,我建议在表上创建更多的索引。这可以大大提高SELECT查询的速度。

这个链接可以解释如何比我更好地创建索引:https://www.postgresqltutorial.com/postgresql-indexes/postgresql-create-index/

我希望这能帮到你。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/72285974

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档