sql – 由连接表中的列进行的慢查询排序

>将生成的元组复制到一些临时内存中
>排序它们(希望在内存中,否则使用磁盘)
>将结果传输到客户端

我想念的是为什么只从连接表中添加一列产生如此不同的性能.

查询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

This question可能是相关的.

编辑：添加了更多细节

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,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

解决方法

在两个查询中,您都返回product_product的所有行,但在第一种情况下,您按django_site列进行排序,因此必须另外检索django_site.domain,这需要额外费用.但不会解释这个巨大的差异.

product_product中的行的物理顺序很可能已经根据列排序,这使得案例2中的排序非常便宜并且案例1中的排序很昂贵.

在“更多细节添加”之后：
它也相当昂贵,因此按字符变化(100)排序比按整数列排序.除了整数小得多之外,还有整理支持可以减慢你的速度.要验证,请尝试使用COLLATE“C”进行订购.阅读更多关于collation support in the manual.
如果您正在运行PostgreSQL 9.1.我现在看到,你有PostgreSQL 8.4.

显然,当您在p.site_id = 1上进行过滤时,查询输出中的所有行都具有相同的django_site.domain值.如果查询规划器更智能,它可能会跳过第一列以便开始排序.

你运行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.name;

有什么区别？ – >显然这并没有解释它……

好的,为了测试varchar(100)是否有所不同,我重新创建了你的场景.见separate answer with a detailed test case and benchmark.这个答案已经超载了.

把它们加起来：
事实证明,我的另一种解释很合适.减速的主要原因显然是根据locale (LC_COLLATE)按varchar(100)列进行排序.

我添加了一些解释和链接到test case.结果应该说明一切.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!