Postgres按外键排序？ [英] Postgres order by foreign key performance?
问题描述
我有奇怪的（？）问题与Postgres通过外键排序。它是第二个表&
I have strange (?) problem with ordering in Postgres by foreign key. It's second table & query that takes much longer with order by than without.
EXPLAIN ANALYZE SELECT "spoleczniak_zdjecia"."id", "spoleczniak_zdjecia"."postac_id", "spoleczniak_zdjecia"."zdjecie", "spoleczniak_zdjecia"."opis", "spoleczniak_zdjecia"."data", "spoleczniak_zdjecia"."avatar", "spoleczniak_zdjecia"."tagi", "postac_postacie"."id", "postac_postacie"."user_id", "postac_postacie"."avatar", "postac_postacie"."ikonka", "postac_postacie"."imie", "postac_postacie"."nazwisko", "postac_postacie"."pseudonim", "postac_postacie"."plec", "postac_postacie"."wzrost", "postac_postacie"."waga", "postac_postacie"."ur_tydz", "postac_postacie"."ur_rok", "postac_postacie"."ur_miasto_id", "postac_postacie"."akt_miasto_id", "postac_postacie"."kasa", "postac_postacie"."punkty", "postac_postacie"."zmeczenie", "postac_postacie"."zdrowie", "postac_postacie"."kariera" FROM "spoleczniak_zdjecia" INNER JOIN "taggit_taggeditem" ON ("spoleczniak_zdjecia"."id" = "taggit_taggeditem"."object_id") INNER JOIN "taggit_tag" ON ("taggit_taggeditem"."tag_id" = "taggit_tag"."id") INNER JOIN "postac_postacie" ON ("spoleczniak_zdjecia"."postac_id" = "postac_postacie"."id") WHERE ("taggit_tag"."slug" = 'ja' AND "taggit_taggeditem"."content_type_id" = 922 ) ORDER BY "spoleczniak_zdjecia"."id" DESC LIMIT 28;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=27.88..27.89 rows=7 width=198) (actual time=2984.689..2984.697 rows=28 loops=1)
-> Sort (cost=27.88..27.89 rows=7 width=198) (actual time=2984.688..2984.692 rows=28 loops=1)
Sort Key: spoleczniak_zdjecia.id
Sort Method: top-N heapsort Memory: 32kB
-> Nested Loop (cost=2.31..27.78 rows=7 width=198) (actual time=1.063..2974.901 rows=9091 loops=1)
-> Nested Loop (cost=2.31..22.02 rows=7 width=109) (actual time=1.057..2899.010 rows=9091 loops=1)
-> Nested Loop (cost=2.31..19.92 rows=7 width=4) (actual time=1.046..2848.853 rows=9103 loops=1)
-> Index Scan using taggit_tag_slug on taggit_tag (cost=0.00..4.27 rows=1 width=4) (actual time=0.025..0.027 rows=1 loops=1)
Index Cond: ((slug)::text = 'ja'::text)
-> Bitmap Heap Scan on taggit_taggeditem (cost=2.31..15.56 rows=7 width=8) (actual time=1.019..2847.244 rows=9103 loops=1)
Recheck Cond: (tag_id = taggit_tag.id)
Filter: (content_type_id = 922)
-> Bitmap Index Scan on taggit_taggeditem_tag_id (cost=0.00..2.31 rows=7 width=0) (actual time=0.954..0.954 rows=9103 loops=1)
Index Cond: (tag_id = taggit_tag.id)
-> Index Scan using spoleczniak_zdjecia_pkey on spoleczniak_zdjecia (cost=0.00..0.29 rows=1 width=109) (actual time=0.005..0.005 rows=1 loops=9103)
Index Cond: (id = taggit_taggeditem.object_id)
-> Index Scan using postac_postacie_pkey on postac_postacie (cost=0.00..0.81 rows=1 width=89) (actual time=0.007..0.007 rows=1 loops=9091)
Index Cond: (id = spoleczniak_zdjecia.postac_id)
Total runtime: 2984.760 ms
这里没有按顺序：
EXPLAIN ANALYZE SELECT "spoleczniak_zdjecia"."id", "spoleczniak_zdjecia"."postac_id", "spoleczniak_zdjecia"."zdjecie", "spoleczniak_zdjecia"."opis", "spoleczniak_zdjecia"."data", "spoleczniak_zdjecia"."avatar", "spoleczniak_zdjecia"."tagi", "postac_postacie"."id", "postac_postacie"."user_id", "postac_postacie"."avatar", "postac_postacie"."ikonka", "postac_postacie"."imie", "postac_postacie"."nazwisko", "postac_postacie"."pseudonim", "postac_postacie"."plec", "postac_postacie"."wzrost", "postac_postacie"."waga", "postac_postacie"."ur_tydz", "postac_postacie"."ur_rok", "postac_postacie"."ur_miasto_id", "postac_postacie"."akt_miasto_id", "postac_postacie"."kasa", "postac_postacie"."punkty", "postac_postacie"."zmeczenie", "postac_postacie"."zdrowie", "postac_postacie"."kariera" FROM "spoleczniak_zdjecia" INNER JOIN "taggit_taggeditem" ON ("spoleczniak_zdjecia"."id" = "taggit_taggeditem"."object_id") INNER JOIN "taggit_tag" ON ("taggit_taggeditem"."tag_id" = "taggit_tag"."id") INNER JOIN "postac_postacie" ON ("spoleczniak_zdjecia"."postac_id" = "postac_postacie"."id") WHERE ("taggit_tag"."slug" = 'ja' AND "taggit_taggeditem"."content_type_id" = 922 ) LIMIT 28;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2.31..27.78 rows=7 width=198) (actual time=1.113..1.482 rows=28 loops=1)
-> Nested Loop (cost=2.31..27.78 rows=7 width=198) (actual time=1.112..1.477 rows=28 loops=1)
-> Nested Loop (cost=2.31..22.02 rows=7 width=109) (actual time=1.102..1.292 rows=28 loops=1)
-> Nested Loop (cost=2.31..19.92 rows=7 width=4) (actual time=1.092..1.145 rows=28 loops=1)
-> Index Scan using taggit_tag_slug on taggit_tag (cost=0.00..4.27 rows=1 width=4) (actual time=0.017..0.017 rows=1 loops=1)
Index Cond: ((slug)::text = 'ja'::text)
-> Bitmap Heap Scan on taggit_taggeditem (cost=2.31..15.56 rows=7 width=8) (actual time=1.072..1.118 rows=28 loops=1)
Recheck Cond: (tag_id = taggit_tag.id)
Filter: (content_type_id = 922)
-> Bitmap Index Scan on taggit_taggeditem_tag_id (cost=0.00..2.31 rows=7 width=0) (actual time=0.989..0.989 rows=9103 loops=1)
Index Cond: (tag_id = taggit_tag.id)
-> Index Scan using spoleczniak_zdjecia_pkey on spoleczniak_zdjecia (cost=0.00..0.29 rows=1 width=109) (actual time=0.004..0.005 rows=1 loops=28)
Index Cond: (id = taggit_taggeditem.object_id)
-> Index Scan using postac_postacie_pkey on postac_postacie (cost=0.00..0.81 rows=1 width=89) (actual time=0.005..0.005 rows=1 loops=28)
Index Cond: (id = spoleczniak_zdjecia.postac_id)
Total runtime: 1.562 ms
什么会导致问题？它是查询？配置？任何特定的配置应该检查？在我最后一个问题有更复杂的查询，但该查询是不复杂的。有任何建议吗？
What can cause problem? It's query? Config? Any particular config should I check? In my last question there was more complex query, but that query is not complex at all. Any suggestions?
和btw。该查询由Django生成（django-taggit要精确）。
和btw。第二部分，它不是很差的硬件（i7，16 GB的内存，RAID 10 3x2为操作系统和数据+ 2 RAID1磁盘为WAL，512 MB的RAID缓存+ BBU）
And btw. that query is generated by Django (django-taggit to be precise). And btw. part II, it's not poor hardware at all (i7, 16 GB of RAM, RAID 10 3x2 for OS and data + 2 RAID1 disks for WAL, 512 MB of RAID cache + BBU)
纯文字查询：
选择spoleczniak_zdjecia，id，spoleczniak_zdjecia，postac_id，spoleczniak_zdjecia，zdjecie，spoleczniak_zdjecia opis，spoleczniak_zdjecia，data，spoleczniak_zdjecia，avatar，spoleczniak_zdjecia，tagi，postac_postacie，id，postac_postacie，user_id，postac_postacie。 avatar，postac_postacie，ikonka，postac_postacie，imie，postac_postacie，nazwisko，postac_postacie，pseudonim，postac_postacie，plec，postac_postacie ，postac_postacie，postac_postacie，postac_postacie，postac_postacie，ur_rod，postac_postacie postac_postacie。punkty，postac_postaciezmeczenie，postac_postacie。zdrowie，postac_postacie。karieraFROMspoleczniak_zdjeciaINNER JOINtaggit_taggeditemON（spoleczniak_zdjecia。id= taggit_taggeditem。object_id）INNER JOINtaggit_tagON（taggit_taggeditem。tag_id=taggit_tag。id）INNER JOINpostac_postacieON（spoleczniak_zdjeciapostac_id=postac_postacie。 id）WHERE（taggit_tag。slug='ja'ANDtaggit_taggeditem。content_type_id= 922）ORDER BYspoleczniak_zdjecia。idDESC LIMIT 28;
SELECT "spoleczniak_zdjecia"."id", "spoleczniak_zdjecia"."postac_id", "spoleczniak_zdjecia"."zdjecie", "spoleczniak_zdjecia"."opis", "spoleczniak_zdjecia"."data", "spoleczniak_zdjecia"."avatar", "spoleczniak_zdjecia"."tagi", "postac_postacie"."id", "postac_postacie"."user_id", "postac_postacie"."avatar", "postac_postacie"."ikonka", "postac_postacie"."imie", "postac_postacie"."nazwisko", "postac_postacie"."pseudonim", "postac_postacie"."plec", "postac_postacie"."wzrost", "postac_postacie"."waga", "postac_postacie"."ur_tydz", "postac_postacie"."ur_rok", "postac_postacie"."ur_miasto_id", "postac_postacie"."akt_miasto_id", "postac_postacie"."kasa", "postac_postacie"."punkty", "postac_postacie"."zmeczenie", "postac_postacie"."zdrowie", "postac_postacie"."kariera" FROM "spoleczniak_zdjecia" INNER JOIN "taggit_taggeditem" ON ("spoleczniak_zdjecia"."id" = "taggit_taggeditem"."object_id") INNER JOIN "taggit_tag" ON ("taggit_taggeditem"."tag_id" = "taggit_tag"."id") INNER JOIN "postac_postacie" ON ("spoleczniak_zdjecia"."postac_id" = "postac_postacie"."id") WHERE ("taggit_tag"."slug" = 'ja' AND "taggit_taggeditem"."content_type_id" = 922 ) ORDER BY "spoleczniak_zdjecia"."id" DESC LIMIT 28;
推荐答案
差别就在这里EXPLAIN输出的第二行：
The difference is right here in the second line of the EXPLAIN output:
-> Sort (cost=27.88..27.89 rows=7 width=198) (actual time=2984.688..2984.692 rows=28 loops=1)
请注意，实际时间几乎是查询的整个时间。排序不仅需要一堆比较（即排序任何东西的成本），而且还需要额外的数据管理，服务器需要将一些数据（行或指针到行）复制到临时位置，以便可以排序而不干扰任何其他。
Notice that the "actual time" is pretty much the entire time of the query. Sorting requires not only a bunch of comparisons (i.e. the cost of sorting anything) but also extra data management, the server needs to copy some data (rows or pointers to rows) to a temporary location so that it can be sorted without disturbing anything else.
任何查询都需要更长的排序时间，除非你幸运，并且排序与磁盘上的顺序匹配，并且优化器可以注意到它们匹配。
Any query will take longer with sorting unless you get lucky and your sorting matches the order on disk and optimizer can notice that they match up.
这篇关于Postgres按外键排序？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
