优化Postgres查询的时间范围和IN值
优化Postgres查询的时间范围和IN值
提问于 2019-07-11 04:16:53
我有一张桌子，每天大约有2000万新的船位记录。我想查询几个不同的船只，并获得任何时期的记录。当我将它用于在线查询时，我希望平均响应时间小于1秒，但我可以活几秒钟。
当查询单个容器时，我得到了一个很好的查询响应时间，但是一旦我开始添加多个容器，查询就会变得缓慢。每当查询超过30秒时，我就有一个常规超时，响应时间大约为4分钟。
我在Postgres文档中阅读了组合多指标，据我所知，对多个值进行查找并不是最理想的，因为它实际上必须根据索引重新检查每个值？
系统可以在多个索引扫描中形成和或条件。例如，像WHERE = 42 OR x = 47 OR x= 53 OR x= 99这样的查询可以分解为对x上的索引进行四次单独扫描，每次扫描使用一个查询子句。然后将这些扫描的结果合并到ORed以生成结果。
这让我想知道，我是否有可能以我想要的速度成功地做到这一点？我是否应该查看不同的DB或表布局，以便能够输入这类请求？
表布局及其索引，该表也被分区，这就是为什么查询计划看起来有点奇怪的原因：
create table mmsi_positions_archive
(
id bigserial not null
constraint mmsi_positions_archive_pkey
primary key,
position_id uuid,
previous_id uuid,
mmsi bigint not null,
collection_type varchar not null,
accuracy numeric,
maneuver numeric,
rate_of_turn numeric,
status integer,
speed numeric,
course numeric,
heading numeric,
position geometry(Point,4326),
timestamp timestamp with time zone not null,
updated_at timestamp with time zone default now(),
created_at timestamp with time zone default now()
);
create index ix_mmsi_positions_archive_position_id
on mmsi_positions_archive (position_id);
create index ix_mmsi_positions_archive_timestamp_id
on mmsi_positions_archive (timestamp, id);
create index ix_mmsi_positions_archive_mmsi_timestamp_id
on mmsi_positions_archive (mmsi, timestamp, id);使用此查询为单个MMSI获取一批职位：
SELECT mmsi_positions_archive.id AS mmsi_positions_archive_id,
mmsi_positions_archive.position_id AS mmsi_positions_archive_position_id,
mmsi_positions_archive.previous_id AS mmsi_positions_archive_previous_id,
mmsi_positions_archive.mmsi AS mmsi_positions_archive_mmsi,
mmsi_positions_archive.collection_type AS mmsi_positions_archive_collection_type,
mmsi_positions_archive.accuracy AS mmsi_positions_archive_accuracy,
mmsi_positions_archive.course AS mmsi_positions_archive_course,
mmsi_positions_archive.heading AS mmsi_positions_archive_heading,
mmsi_positions_archive.maneuver AS mmsi_positions_archive_maneuver,
mmsi_positions_archive.rate_of_turn AS mmsi_positions_archive_rate_of_turn,
mmsi_positions_archive.speed AS mmsi_positions_archive_speed,
mmsi_positions_archive.status AS mmsi_positions_archive_status,
concat(
ST_X(mmsi_positions_archive.position),
';',
ST_Y(mmsi_positions_archive.position)
) AS mmsi_positions_archive_position,
mmsi_positions_archive.timestamp AS mmsi_positions_archive_timestamp,
mmsi_positions_archive.created_at AS mmsi_positions_archive_created_at,
vessels_v2.vessel_id AS vessel_id
FROM (
SELECT *
FROM mmsi_positions_archive
WHERE mmsi_positions_archive.timestamp >= '2019-01-01 00:00:00+00:00'
AND mmsi IN (SELECT vessels_v2.mmsi
FROM vessels_v2
WHERE vessels_v2.vessel_id IN ('c2cba910-c7fa-4efe-9531-1cd30521e6e0'))
AND mmsi_positions_archive.timestamp <= '2019-01-14 00:00:00+00:00'
ORDER BY mmsi_positions_archive.timestamp ASC, mmsi_positions_archive.id ASC
LIMIT 1000
) AS mmsi_positions_archive
JOIN vessels_v2 ON vessels_v2.mmsi = mmsi_positions_archive.mmsi
ORDER BY mmsi_positions_archive.timestamp ASC, mmsi_positions_archive.id ASC;提供以下查询计划：
Nested Loop (cost=2834.44..7350.62 rows=580 width=150) (actual time=1772.151..1777.071 rows=1000 loops=1)
-> Limit (cost=2834.02..2835.47 rows=580 width=142) (actual time=1772.114..1772.286 rows=1000 loops=1)
-> Sort (cost=2834.02..2835.47 rows=580 width=142) (actual time=1772.113..1772.218 rows=1000 loops=1)
" Sort Key: mmsi_positions_archive.""timestamp"", mmsi_positions_archive.id"
Sort Method: quicksort Memory: 370kB
-> Nested Loop (cost=0.42..2807.40 rows=580 width=142) (actual time=0.032..1769.592 rows=1209 loops=1)
-> Index Scan using ix_vessels_v2_vessel_id on vessels_v2 vessels_v2_1 (cost=0.42..8.44 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
Index Cond: (vessel_id = 'c2cba910-c7fa-4efe-9531-1cd30521e6e0'::uuid)
-> Append (cost=0.00..2792.05 rows=691 width=134) (actual time=0.020..1768.818 rows=1209 loops=1)
-> Seq Scan on mmsi_positions_archive (cost=0.00..0.00 rows=1 width=324) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (("timestamp" >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2019-01-14 00:00:00+00'::timestamp with time zone) AND (vessels_v2_1.mmsi = mmsi))
-> Index Scan using mmsi_positions_archive_p2019q1_mmsi_timestamp_id_idx on mmsi_positions_archive_p2019q1 (cost=0.70..2792.05 rows=690 width=134) (actual time=0.017..1768.526 rows=1209 loops=1)
Index Cond: ((mmsi = vessels_v2_1.mmsi) AND ("timestamp" >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2019-01-14 00:00:00+00'::timestamp with time zone))
-> Index Scan using ix_vessels_v2_mmsi on vessels_v2 (cost=0.42..7.76 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=1000)
Index Cond: (mmsi = mmsi_positions_archive.mmsi)
Planning time: 1.740 ms
Execution time: 1777.251 ms不是最快的反应，但我没意见。
但是，如果我再添加99 vessel_id来查找(我也尝试过使用EXISTS和IN，并且是相同的查询计划)，那么它的运行速度会慢得多，但看起来几乎是一样的。我想大概是线性的：
Nested Loop (cost=283506.49..290868.07 rows=1000 width=150) (actual time=242910.968..242916.242 rows=1000 loops=1)
-> Limit (cost=283506.07..283508.57 rows=1000 width=142) (actual time=242910.928..242911.198 rows=1000 loops=1)
-> Sort (cost=283506.07..283651.18 rows=58045 width=142) (actual time=242910.927..242911.132 rows=1000 loops=1)
" Sort Key: mmsi_positions_archive.""timestamp"", mmsi_positions_archive.id"
Sort Method: top-N heapsort Memory: 314kB
-> Nested Loop (cost=810.44..280323.53 rows=58045 width=142) (actual time=1.466..242642.459 rows=198040 loops=1)
-> HashAggregate (cost=810.44..811.44 rows=100 width=8) (actual time=0.457..0.596 rows=100 loops=1)
Group Key: vessels_v2_1.mmsi
-> Bitmap Heap Scan on vessels_v2 vessels_v2_1 (cost=439.02..810.19 rows=100 width=8) (actual time=0.274..0.443 rows=100 loops=1)
" Recheck Cond: (vessel_id = ANY ('{c2cba910-c7fa-4efe-9531-1cd30521e6e0,e7f63085-0c72-5bbb-ba3f-e8ea77ed8f9a,6d42935b-e709-4891-ad0d-9f7a5b44b6b9,88a29bc8-9068-47ab-8508-52a64c8f57f2,e0ae6213-289e-59a8-8f13-1463b83f6937,58ffb912-0d7a-54a4-b105-9b97dcc80363,c18c0067-059f-47d3-8ab2-39ce599b4442,e687fae4-795a-5f96-96fe-181084285a5d,e1c3b3c1-8a56-4d4e-96ac-55e459241538,85313219-ba1f-4d59-b852-46f681faddd0,41832283-52f9-4a62-a8c8-cc9241c6d463,008320d4-7560-47f2-97e1-6f558aa3425d,b9e289f4-5048-47db-9ad6-d856be1c13ef,bbf7dfb1-fe61-4587-a0ba-1c88505fd820,3bf79bc9-5707-4aeb-8225-c2ca58a6b2c1,20b658d2-cf7d-4055-84d0-b613141c2e50,7dbeecfc-99f1-4584-83f3-9436986c8df0,869cada5-c9b8-4a02-8390-acfd13f26ca3,7e27d598-a8d6-49fc-81d2-222dd4b86287,41442ada-e4e6-4886-9356-261f7e9af03f,7d594b52-bc55-472f-ba4c-aa15029e2997,238494ef-1059-585e-8a86-ff49a85e0245,af9752e8-1436-4f5b-91da-09d9a296c865,245dd437-4873-4c43-9c12-1f50198e9572,8b5d47ac-ada9-41b6-82d0-f57e9fafad36,4114283a-a00b-5b48-a764-13e15f5ffe0c,188b212e-0834-47ee-bb86-20bb7358891f,a56ba9c7-4215-4717-93c8-410d3bb9921f,e6f4680b-6cd8-4331-9025-8f8ecd35c77c,90027158-73aa-4a52-9201-fb314f59b812,ab1a1e1f-2c33-4e99-a0bc-d4957f13c36f,812e80d7-2d48-4f2d-be65-8bf73577712d,90a48902-e646-490a-8d75-3e2d94f64ffb,26447406-6057-49be-809c-3d11897c17e6,041126f0-c9a0-4b4d-8cf1-4b9a05fa1fbb,b1e0c45d-50ec-4435-bc29-5c428b445e8f,9add606d-68fd-57a0-b9e1-21139d0eef12,9af608f1-21c2-44b7-866a-496f9d707fb3,30b1a71e-949e-43ff-84b6-8457ed71b387,31385944-0c19-4e6f-afe9-dda7ad37a10b,9a9abe28-c5e1-4df2-8d90-7f4a137d4d50,f97ba534-847d-4f4f-a40b-fc0ea7a6291a,c86b0d7a-53ed-40b9-b15d-cd8b21a5b467,7ef2abef-0db5-47f1-a984-e96132401534,f4474469-f17b-414c-8b81-79e9623e66f1,85686cd5-86b4-58ce-9d63-ca8cdc74fdf0,4ac608f5-d23a-4fb2-8137-530d52be8613,38c59a1a-8c5d-5517-83e7-17010a624cc8,8217b553-0c3a-43dc-ac91-660acd350597,cfef6384-41af-47ec-8438-90bc2a2bac34,beafb43c-6af8-476a-b51c-7aa9c4a62d14,380bf1b1-4424-4606-a186-3cdb09693dc6,2c904fd8-68b8-4531-8dfb-8cd0b9dbb026,8e24703d-3f99-5a85-8489-f189aac7b4ef,8f4e90d0-fa33-4a74-a842-7922cde86edd,c4a77fa0-fc82-5335-93bb-2813c85c0ab7,128da36f-2494-412f-b669-0d70fb558065,eeed333b-6d71-4401-b5e4-a80e6224850e,b87a2d57-f1fc-4209-bacf-9dc1edffd121,89e55083-d6d1-4eab-801e-75b358a3beff,61850f9d-259f-40b5-a35b-64c3dfbd9e46,19806582-2966-41d0-b5ed-42849adc4ac7,ab0e9346-6c70-412f-aba7-cac2e6e8740b,83140b9d-54e7-4c6e-b1f2-30fedf0750ad,aaf239ef-09a0-4807-b889-72b6014e0f8b,682077b3-cc2c-5881-bab1-2249e72aa09b,4f1cd57c-470e-44a8-83f2-958f49d22be5,4c9d1a1a-b0e8-503b-b670-9c209bcc0cb7,7d050be6-b922-4a5c-88dc-4417e17f5da2,25addffa-0375-5065-ad7e-27461218f92b,f0e00d3c-37f7-4801-b384-a30931d64dee,861214ae-a0fb-5b57-a0ba-11bb29d674e5,357fb0ff-b21c-4c62-812d-2360ea326044,bf91f821-aa01-4f7e-9b46-62950dc1ef4b,442d6006-c407-454c-95ca-b23a9b2aaeea,18703f60-49d9-53fa-87bc-bd4e5e80a888,08511089-ce83-58bf-badd-4bd8dbb67213,5c311c46-9996-4bab-ad31-9d4f5a75be7d,5b1cacd9-e221-4892-bc6e-855840ed270a,58142628-6a6f-4f6d-8a19-bffc7430fe48,d7b6f3d9-87f9-484a-84e8-b4a3cfb3b450,01f09b54-deca-400d-820b-7b099ba07376,ec6690a2-c4fb-4dfe-8d1b-6e47341d893a,7473a9f5-a5df-4aea-b0dd-1942f4fab384,46015f3e-1ef3-4ec6-a801-19a3d136fab6,52014875-a316-469b-99dc-e8ec7a3812f1,2c467523-3623-48db-a791-0ee89d340c40,6fd426a1-dae0-48ca-bd80-d32c2feb94ca,a02739f1-62b5-4af4-8acc-78728cdb9ff8,ed20ffd0-dd17-4a7b-935a-446debf736a2,f8743fee-2129-4776-8857-3cc34feac9a2,514d19d9-8900-5021-b373-94e14f96b7f3,32f5295e-efe4-44f9-82e1-8fe9c643925a,e156963e-344f-4e56-ba7e-b40be6754660,a37b5dd4-2086-4f05-b297-c60b46d0ddc3,1eda7fb1-21dc-4a7e-82e1-aa8c5308d240,a3585721-8b2c-4e27-b077-64526eec1136,89a30ede-8b8e-5d62-9da0-365cd059fcda,688dc661-e54c-4585-bedf-8817a5aa3aad,5aa5f47f-379d-48ff-b122-b13561d6e1ea}'::uuid[]))"
Heap Blocks: exact=99
-> Bitmap Index Scan on ix_vessels_v2_vessel_id (cost=0.00..439.00 rows=100 width=0) (actual time=0.263..0.263 rows=102 loops=1)
" Index Cond: (vessel_id = ANY ('{c2cba910-c7fa-4efe-9531-1cd30521e6e0,e7f63085-0c72-5bbb-ba3f-e8ea77ed8f9a,6d42935b-e709-4891-ad0d-9f7a5b44b6b9,88a29bc8-9068-47ab-8508-52a64c8f57f2,e0ae6213-289e-59a8-8f13-1463b83f6937,58ffb912-0d7a-54a4-b105-9b97dcc80363,c18c0067-059f-47d3-8ab2-39ce599b4442,e687fae4-795a-5f96-96fe-181084285a5d,e1c3b3c1-8a56-4d4e-96ac-55e459241538,85313219-ba1f-4d59-b852-46f681faddd0,41832283-52f9-4a62-a8c8-cc9241c6d463,008320d4-7560-47f2-97e1-6f558aa3425d,b9e289f4-5048-47db-9ad6-d856be1c13ef,bbf7dfb1-fe61-4587-a0ba-1c88505fd820,3bf79bc9-5707-4aeb-8225-c2ca58a6b2c1,20b658d2-cf7d-4055-84d0-b613141c2e50,7dbeecfc-99f1-4584-83f3-9436986c8df0,869cada5-c9b8-4a02-8390-acfd13f26ca3,7e27d598-a8d6-49fc-81d2-222dd4b86287,41442ada-e4e6-4886-9356-261f7e9af03f,7d594b52-bc55-472f-ba4c-aa15029e2997,238494ef-1059-585e-8a86-ff49a85e0245,af9752e8-1436-4f5b-91da-09d9a296c865,245dd437-4873-4c43-9c12-1f50198e9572,8b5d47ac-ada9-41b6-82d0-f57e9fafad36,4114283a-a00b-5b48-a764-13e15f5ffe0c,188b212e-0834-47ee-bb86-20bb7358891f,a56ba9c7-4215-4717-93c8-410d3bb9921f,e6f4680b-6cd8-4331-9025-8f8ecd35c77c,90027158-73aa-4a52-9201-fb314f59b812,ab1a1e1f-2c33-4e99-a0bc-d4957f13c36f,812e80d7-2d48-4f2d-be65-8bf73577712d,90a48902-e646-490a-8d75-3e2d94f64ffb,26447406-6057-49be-809c-3d11897c17e6,041126f0-c9a0-4b4d-8cf1-4b9a05fa1fbb,b1e0c45d-50ec-4435-bc29-5c428b445e8f,9add606d-68fd-57a0-b9e1-21139d0eef12,9af608f1-21c2-44b7-866a-496f9d707fb3,30b1a71e-949e-43ff-84b6-8457ed71b387,31385944-0c19-4e6f-afe9-dda7ad37a10b,9a9abe28-c5e1-4df2-8d90-7f4a137d4d50,f97ba534-847d-4f4f-a40b-fc0ea7a6291a,c86b0d7a-53ed-40b9-b15d-cd8b21a5b467,7ef2abef-0db5-47f1-a984-e96132401534,f4474469-f17b-414c-8b81-79e9623e66f1,85686cd5-86b4-58ce-9d63-ca8cdc74fdf0,4ac608f5-d23a-4fb2-8137-530d52be8613,38c59a1a-8c5d-5517-83e7-17010a624cc8,8217b553-0c3a-43dc-ac91-660acd350597,cfef6384-41af-47ec-8438-90bc2a2bac34,beafb43c-6af8-476a-b51c-7aa9c4a62d14,380bf1b1-4424-4606-a186-3cdb09693dc6,2c904fd8-68b8-4531-8dfb-8cd0b9dbb026,8e24703d-3f99-5a85-8489-f189aac7b4ef,8f4e90d0-fa33-4a74-a842-7922cde86edd,c4a77fa0-fc82-5335-93bb-2813c85c0ab7,128da36f-2494-412f-b669-0d70fb558065,eeed333b-6d71-4401-b5e4-a80e6224850e,b87a2d57-f1fc-4209-bacf-9dc1edffd121,89e55083-d6d1-4eab-801e-75b358a3beff,61850f9d-259f-40b5-a35b-64c3dfbd9e46,19806582-2966-41d0-b5ed-42849adc4ac7,ab0e9346-6c70-412f-aba7-cac2e6e8740b,83140b9d-54e7-4c6e-b1f2-30fedf0750ad,aaf239ef-09a0-4807-b889-72b6014e0f8b,682077b3-cc2c-5881-bab1-2249e72aa09b,4f1cd57c-470e-44a8-83f2-958f49d22be5,4c9d1a1a-b0e8-503b-b670-9c209bcc0cb7,7d050be6-b922-4a5c-88dc-4417e17f5da2,25addffa-0375-5065-ad7e-27461218f92b,f0e00d3c-37f7-4801-b384-a30931d64dee,861214ae-a0fb-5b57-a0ba-11bb29d674e5,357fb0ff-b21c-4c62-812d-2360ea326044,bf91f821-aa01-4f7e-9b46-62950dc1ef4b,442d6006-c407-454c-95ca-b23a9b2aaeea,18703f60-49d9-53fa-87bc-bd4e5e80a888,08511089-ce83-58bf-badd-4bd8dbb67213,5c311c46-9996-4bab-ad31-9d4f5a75be7d,5b1cacd9-e221-4892-bc6e-855840ed270a,58142628-6a6f-4f6d-8a19-bffc7430fe48,d7b6f3d9-87f9-484a-84e8-b4a3cfb3b450,01f09b54-deca-400d-820b-7b099ba07376,ec6690a2-c4fb-4dfe-8d1b-6e47341d893a,7473a9f5-a5df-4aea-b0dd-1942f4fab384,46015f3e-1ef3-4ec6-a801-19a3d136fab6,52014875-a316-469b-99dc-e8ec7a3812f1,2c467523-3623-48db-a791-0ee89d340c40,6fd426a1-dae0-48ca-bd80-d32c2feb94ca,a02739f1-62b5-4af4-8acc-78728cdb9ff8,ed20ffd0-dd17-4a7b-935a-446debf736a2,f8743fee-2129-4776-8857-3cc34feac9a2,514d19d9-8900-5021-b373-94e14f96b7f3,32f5295e-efe4-44f9-82e1-8fe9c643925a,e156963e-344f-4e56-ba7e-b40be6754660,a37b5dd4-2086-4f05-b297-c60b46d0ddc3,1eda7fb1-21dc-4a7e-82e1-aa8c5308d240,a3585721-8b2c-4e27-b077-64526eec1136,89a30ede-8b8e-5d62-9da0-365cd059fcda,688dc661-e54c-4585-bedf-8817a5aa3aad,5aa5f47f-379d-48ff-b122-b13561d6e1ea}'::uuid[]))"
-> Append (cost=0.00..2788.21 rows=691 width=134) (actual time=0.733..2425.501 rows=1980 loops=100)
-> Seq Scan on mmsi_positions_archive (cost=0.00..0.00 rows=1 width=324) (actual time=0.001..0.001 rows=0 loops=100)
Filter: (("timestamp" >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2019-01-14 00:00:00+00'::timestamp with time zone) AND (vessels_v2_1.mmsi = mmsi))
-> Index Scan using mmsi_positions_archive_p2019q1_mmsi_timestamp_id_idx on mmsi_positions_archive_p2019q1 (cost=0.70..2788.21 rows=690 width=134) (actual time=0.729..2425.096 rows=1980 loops=100)
Index Cond: ((mmsi = vessels_v2_1.mmsi) AND ("timestamp" >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2019-01-14 00:00:00+00'::timestamp with time zone))
-> Index Scan using ix_vessels_v2_mmsi on vessels_v2 (cost=0.42..7.33 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=1000)
Index Cond: (mmsi = mmsi_positions_archive.mmsi)
Planning time: 1.148 ms
Execution time: 242916.404 ms回答 1
Database Administration用户
发布于 2019-07-15 06:49:45
索引扫描也需要很长时间，只有一个vessel_id：
-> Index Scan using mmsi_positions_archive_p2019q1_mmsi_timestamp_id_idx on mmsi_positions_archive_p2019q1 (cost=0.70..2792.05 rows=690 width=134) (actual time=0.017..1768.526 rows=1209 loops=1)
Index Cond: ((mmsi = vessels_v2_1.mmsi) AND ("timestamp" >= '2019-01-01 00:00:00+00'::timestamp with time zone) AND ("timestamp" <= '2019-01-14 00:00:00+00'::timestamp with time zone))1.8秒扫描690行！看到EXPLAIN (ANALYZE, BUFFERS)输出会很有趣，这样我们就可以知道它是低I/O还是有其他问题。
按照您编写查询的方式，带有100 vessel_ids的查询必须执行一个嵌套循环，因此它必须执行100次慢速索引扫描，这是所有时间都花在这里的地方。
将子查询重写到
SELECT *
FROM mmsi_positions_archive
WHERE mmsi_positions_archive.timestamp
BETWEEN '2019-01-01 00:00:00+00:00' AND '2019-01-14 00:00:00+00:00'
AND EXISTS (
SELECT 1 FROM vessels_v2
WHERE vessels_v2.mmsi = mmsi_positions_archive.mmsi
AND vessels_v2.vessel_id IN (/* long list */)
)与IN不同，EXISTS子句可以转换为半连接，这可能会导致更好的执行计划。
页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://dba.stackexchange.com/questions/242604
复制相关文章
相似问题
腾讯云开发者
