通过联接表中的列进行缓慢的查询排序

Question

在查询中引入ORDER BY子句会增加总时间，这是因为数据库需要对排序结果集进行额外的工作：

• 将结果元组复制到一些临时内存中
• 对它们进行排序（希望在内存中，否则使用磁盘）
• 将结果流式传输到客户端

我想念的是为什么仅从联接表中添加列会产生如此不同的性能。

查询1

EXPLAIN ANALYZE
SELECT p.*
FROM product_product p
JOIN django_site d ON (p.site_id = d.id)
WHERE (p.active = true  AND p.site_id = 1 )
ORDER BY d.domain, p.ordering, p.name

查询计划

Sort  (cost=3909.83..3952.21 rows=16954 width=1086) (actual time=1120.618..1143.922 rows=16946 loops=1)
   Sort Key: django_site.domain, product_product.ordering, product_product.name
   Sort Method:  quicksort  Memory: 25517kB
   ->  Nested Loop  (cost=0.00..2718.86 rows=16954 width=1086) (actual time=0.053..87.396 rows=16946 loops=1)
         ->  Seq Scan on django_site  (cost=0.00..1.01 rows=1 width=24) (actual time=0.010..0.012 rows=1 loops=1)
               Filter: (id = 1)
         ->  Seq Scan on product_product  (cost=0.00..2548.31 rows=16954 width=1066) (actual time=0.036..44.138 rows=16946 loops=1)
               Filter: (product_product.active AND (product_product.site_id = 1))
 Total runtime: 1182.515 ms

查询2

与上述相同，但不按 django_site.domain

查询计划

 Sort  (cost=3909.83..3952.21 rows=16954 width=1066) (actual time=257.094..278.905 rows=16946 loops=1)
   Sort Key: product_product.ordering, product_product.name
   Sort Method:  quicksort  Memory: 25161kB
   ->  Nested Loop  (cost=0.00..2718.86 rows=16954 width=1066) (actual time=0.075..86.120 rows=16946 loops=1)
         ->  Seq Scan on django_site  (cost=0.00..1.01 rows=1 width=4) (actual time=0.015..0.017 rows=1 loops=1)
               Filter: (id = 1)
         ->  Seq Scan on product_product  (cost=0.00..2548.31 rows=16954 width=1066) (actual time=0.052..44.024 rows=16946 loops=1)
               Filter: (product_product.active AND (product_product.site_id = 1))
 Total runtime: 305.392 ms

编辑：添加了更多详细信息

           Table "public.product_product"
 Column       |          Type          |  
 -------------+------------------------+---------
 id                | integer                | not null default nextval('product_product_id_seq'::regclass)
 site_id           | integer                | not null
 name              | character varying(255) | not null
 slug              | character varying(255) | not null
 sku               | character varying(255) | 
 ordering          | integer                | not null
 [snip some columns ]

 Indexes:
    "product_product_pkey" PRIMARY KEY, btree (id)
    "product_product_site_id_key" UNIQUE, btree (site_id, sku)
    "product_product_site_id_key1" UNIQUE, btree (site_id, slug)
    "product_product_site_id" btree (site_id)
    "product_product_slug" btree (slug)
    "product_product_slug_like" btree (slug varchar_pattern_ops)


                  Table "public.django_site"
 Column |          Type          | 
--------+------------------------+----------
 id     | integer                | not null default nextval('django_site_id_seq'::regclass)
 domain | character varying(100) | not null
 name   | character varying(50)  | not null
Indexes:
    "django_site_pkey" PRIMARY KEY, btree (id)

Postgres版本是 8.4

# select count(*) from django_site;
 count 
-------
     1

# select count(*) from product_product;
 count 
-------
 17540

# select active, count(*) from product_product group by active;
 active | count 
--------+-------
 f      |   591
 t      | 16949

# select site_id, count(*) from product_product group by site_id;
 site_id | count 
---------+-------
       1 | 17540

Answer 1

在排序操作之前，EXPLAIN ANALYZE的输出是相同的，因此排序会有所不同。

在这两个查询中，您都返回的所有行product_product，但在第一种情况下，您按的列进行排序django_site，因此django_site.domain必须另外检索，这会产生额外的费用。但是不会解释很大的区别。

行中的 物理顺序 很有可能product_product已经根据列ordering进行了排序，这使情况2的排序非常便宜，情况1的排序很昂贵。

后“更多的细节补充”：
这也是 相当昂贵 所以排序character varying(100)不是排序的integer列。除了整数要小得多之外，还有排序规则支持会使您放慢速度。要进行验证，请尝试通过订购COLLATE "C"。在手册中阅读有关归类支持的更多信息。如果您
正在 运行PostgreSQL 9.1。我现在看到，您拥有PostgreSQL 8.4。

显然，查询输出中的所有行在上进行django_site.domain过滤时都具有相同的值p.site_id = 1。如果查询计划者更聪明，则可能会跳过第一列以开始进行排序。

您运行PostgreSQL 8.4。9.1的查询计划器已经变得更加智能。升级可能会改变这种情况，但是我不能肯定地说。

为了验证我有关物理顺序的理论，您可以尝试使用随机插入的行制作大表的副本，然后再次运行查询。像这样：

CREATE TABLE p AS
SELECT *
FROM   public.product_product
ORDER  BY random();

接着：

EXPLAIN ANALYZE
SELECT p.*
FROM   p
JOIN   django_site d ON (p.site_id = d.id)
WHERE  p.active
AND    p.site_id = 1
ORDER  BY d.domain, p.ordering, p.name;

有什么区别吗？->显然，这并不能解释…

OK，要测试是否varchar(100)有所作为，我重新创建了您的方案。请参阅
单独的答案以及详细的测试用例和基准
。这个答案已经超载了。

总结一下：
事实证明，我的其他解释也很合适。速度下降的主要原因显然是varchar(100)根据区域设置（LC_COLLATE）按列排序。