PostgreSQL通过查询优化顺序排列

概述我在这里遇到一个小问题. SELECT DISTINCT ON ("reporting_processedamazonsnapshot"."offer_id") *FROM "reporting_processedamazonsnapshot" INNER JOIN "offers_boooffer" ON ("reporting_processedamazonsna 我在这里遇到一个小问题.

SELECT disTINCT ON ("reporting_processedamazonsnapshot"."offer_ID") *FROM "reporting_processedamazonsnapshot" INNER JOIN      "offers_boooffer"        ON ("reporting_processedamazonsnapshot"."offer_ID" =            "offers_boooffer"."ID") INNER JOIN     "offers_offersettings"        ON ("offers_boooffer"."ID" = "offers_offersettings"."offer_ID")WHERE "offers_offersettings"."account_ID" = 20ORDER BY "reporting_processedamazonsnapshot"."offer_ID" ASC,"reporting_processedamazonsnapshot"."scraPing_date" DESC

我在offer_ID ASC上有一个名为latest_scraPing的索引,scraPing_date DESC但由于某种原因,Postgresql在使用索引后仍在进行排序,导致巨大的性能问题.

我不明白为什么它没有使用已经排序的数据而不是重做排序.我的索引错了吗？或者我应该尝试以另一种方式进行查询？

这是解释

与其实际数据

'Unique  (cost=21260.47..21263.06 rows=519 wIDth=1288) (actual time=38053.685..38177.348 rows=1783 loops=1)''  ->  Sort  (cost=21260.47..21261.76 rows=519 wIDth=1288) (actual time=38053.683..38161.478 rows=153095 loops=1)''        Sort Key: reporting_processedamazonsnapshot.offer_ID,reporting_processedamazonsnapshot.scraPing_date DESC''        Sort Method: external merge  disk: 162088kB''        ->  nested Loop  (cost=41.90..21237.06 rows=519 wIDth=1288) (actual time=70.874..36148.348 rows=153095 loops=1)''              ->  nested Loop  (cost=41.47..17547.90 rows=1627 wIDth=8) (actual time=54.287..126.740 rows=1784 loops=1)''                    ->  Bitmap Heap Scan on offers_offersettings  (cost=41.04..4823.48 rows=1627 wIDth=4) (actual time=52.532..84.102 rows=1784 loops=1)''                          Recheck Cond: (account_ID = 20)''                          Heap Blocks: exact=38''                          ->  Bitmap Index Scan on offers_offersettings_account_ID_fff7a8c0  (cost=0.00..40.63 rows=1627 wIDth=0) (actual time=49.886..49.886 rows=4132 loops=1)''                                Index Cond: (account_ID = 20)''                    ->  Index Only Scan using offers_boooffer_pkey on offers_boooffer  (cost=0.43..7.81 rows=1 wIDth=4) (actual time=0.019..0.020 rows=1 loops=1784)''                          Index Cond: (ID = offers_offersettings.offer_ID)''                          Heap Fetches: 1784''              ->  Index Scan using latest_scraPing on reporting_processedamazonsnapshot  (cost=0.43..1.69 rows=58 wIDth=1288) (actual time=0.526..20.146 rows=86 loops=1784)''                    Index Cond: (offer_ID = offers_boooffer.ID)''Planning time: 187.133 ms''Execution time: 38195.266 ms'

解决方法 要使用索引来避免排序,Postgresql首先必须按索引顺序扫描所有“reporting_processedamazonsnapshot”,然后使用嵌套循环连接加入所有“offers_boooffer”(以便保留顺序),然​​后加入所有“ offers_offersettings“,再次使用嵌套循环连接.

最后,所有与条件“offers_offersettings”不匹配的行.“account_ID”= 20将被丢弃.

Postgresql认为 – 正确地说在我看来 – 使用条件尽可能地减少行数更有效率,然后使用最有效的join方法来连接表,然后对disTINCT子句进行排序.

我想知道以下查询是否可能更快：

SELECT disTINCT ON (q.offer_ID) *FROM offers_offersettings ofs   JOIN offers_boooffer bo ON bo.ID = ofs.offer_ID   CROSS JOIN LAteraL      (SELECT *       FROM reporting_processedamazonsnapshot r       WHERE r.offer_ID = bo.offer_ID       ORDER BY r.scraPing_date DESC       liMIT 1) qWHERE ofs.account_ID = 20ORDER BY q.offer_ID ASC,q.scraPing_date DESC;

执行计划将类似,只是必须从索引扫描更少的行,这将减少您最需要的执行时间.

如果要加快排序速度,请将该查询的work_mem增加到大约500MB(如果可以负担的话).

总结

以上是内存溢出为你收集整理的PostgreSQL通过查询优化顺序排列全部内容，希望文章能够帮你解决PostgreSQL通过查询优化顺序排列所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。