加入表中列的查询排序缓慢

Introducing an ORDER BY clause in a query increases the total time due the extra work that the db have to do in order to sort the result set:

在查询中引入ORDER BY子句会增加总时间,因为db必须执行额外的工作才能对结果集进行排序:

• copy the resulting tuples in some temporary memory

将生成的元组复制到一些临时内存中

• sorting them (hopefully in memory, otherwise using the disk)

对它们进行排序(希望在内存中,否则使用磁盘)

• stream the result to the client

将结果传输到客户端

What I miss is why just adding a column from a joined table produces a so different performance.

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

Query1

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

Query plan

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

Query 2

Same as the above but not sorting by django_site.domain

与上面相同,但没有按django_site.domain排序

Query plan

 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 could be related.

这个问题可能有关系。

Edit: More details added

           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)

The Postgres version is 8.4

Postgres版本是8.4

some table stats:

# 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

3 个解决方案

#1

The output of EXPLAIN ANALYZE is identical up to the sort operation, so sorting makes the difference.

EXPLAIN ANALYZE的输出与排序操作完全相同,因此排序会产生差异。

In both queries you return all rows of product_product, but in the first case you sort by a column of django_site, so django_site.domain has to be retrieved in addition, that costs extra. But would not explain the big difference.

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

There is a good chance that the physical order of the rows in product_product is already according to the column ordering, which makes the sort in case 2 very cheap and the sort in case 1 expensive.

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

---

After "more details added":
It is also considerably more expensive so sort by character varying(100) than to sort by an integer column. In addition to integer being much smaller, there is also the collation support that slows you down. To verify, try ordering with COLLATE "C". Read more about collation support in the manual. If you were running PostgreSQL 9.1. I see now, that you have PostgreSQL 8.4.

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

Obviously, all rows in the query output have the same value for django_site.domain as you filter on p.site_id = 1. If the query planner was smarter, it might skip the first column for ordering to begin with.

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

You run PostgreSQL 8.4. The query planner of 9.1 has become considerably more intelligent. Upgrading might change the situation, but I can't say for certain.

你运行PostgreSQL 8.4。 9.1的查询规划器变得更加智能化。升级可能会改变这种情况,但我不能肯定地说。

---

To verify my theory about physical ordering, you could try and make a copy of your big table with the rows inserted in random order and then run the queries again. Like this:

要验证我关于物理排序的理论,您可以尝试使用随机顺序插入的行制作大表的副本,然后再次运行查询。喜欢这个:

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

And then:

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;

Any difference? --> Obviously that doesn't explain it ...

有什么区别? - >显然不解释它......

---

OK, to test whether the varchar(100) makes the difference, I recreated your scenario. See the separate answer with a detailed test case and benchmark. This answer is overloaded already.

好的,为了测试varchar(100)是否有所不同,我重新创建了你的场景。请参阅具有详细测试用例和基准的单独答案。这个答案已经超载了。

To sum it up:
Turns out, my other explanation fits. The main reason for the slowdown is obviously sorting by a varchar(100) column according to a locale (LC_COLLATE).

总结一下:原来,我的另一个解释是合适的。减速的主要原因显然是根据区域设置(LC_COLLATE)按varchar(100)列进行排序。

I added some explanation and links to the test case. The results should speak for themselves.

我添加了一些解释和测试用例的链接。结果应该说明一切。

#2

Test Case

PostgreSQL 9.1. Test database with limited resources, but way enough for this small case. The locale for collation will be relevant:

PostgreSQL 9.1。使用有限的资源测试数据库,但对于这种小案例足够了。整理的区域设置将是相关的:

SHOW LC_COLLATE;

 de_AT.UTF-8

Step 1) Reconstruct raw test environment

步骤1)重建原始测试环境

-- DROP TABLE x;
CREATE SCHEMA x;  -- test schema

-- DROP TABLE x.django_site;
CREATE TABLE x.django_site (
id serial primary key
,domain character varying(100) not null
,int_col int not null
);
INSERT INTO x.django_site values (1,'www.testsite.com/foodir/', 3);

-- DROP TABLE x.product;
CREATE TABLE x.product (
 id serial primary key
,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
,active boolean not null
);

INSERT INTO x.product (site_id, name, slug, sku, ordering, active)
SELECT 1
    ,repeat(chr((random() * 255)::int + 32), (random()*255)::int)
    ,repeat(chr((random() * 255)::int + 32), (random()*255)::int)
    ,repeat(chr((random() * 255)::int + 32), (random()*255)::int)
    ,i -- ordering in sequence
    ,NOT (random()* 0.5174346569119122)::int::bool
FROM generate_series(1, 17540) AS x(i);
-- SELECT ((591::float8 / 17540)* 0.5) / (1 - (591::float8 / 17540))
-- = 0.5174346569119122

CREATE INDEX product_site_id on x.product(site_id);

Step 2) ANALYZE

步骤2)分析

    ANALYZE x.product;
    ANALYZE x.django_site;

Step 3) Reorder BY random()

步骤3)随机重新排序()

-- DROP TABLE x.p;
CREATE TABLE x.p AS
SELECT *
FROM   x.product
ORDER  BY random();

ANALYZE x.p;

---

Results

EXPLAIN ANALYZE
    SELECT p.*
    FROM   x.p
    JOIN   x.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
--    ORDER  BY p.ordering, p.name
--    ORDER  BY d.id, p.ordering, p.name
--    ORDER  BY d.int_col, p.ordering, p.name
--    ORDER  BY p.name COLLATE "C"
--    ORDER  BY d.domain COLLATE "C", p.ordering, p.name -- dvd's final solution

1) Pre ANALYZE (-> bitmap index scan)
2) Post ANALYZE (-> seq scan)
3) Re-order by random(), ANALYZE

1)预分析( - >位图索引扫描)2)分析后( - > seq扫描)3)通过随机(),分析重新排序

ORDER  BY d.domain, p.ordering, p.name

1) Total runtime: 1253.543 ms
2) Total runtime: 1250.351 ms
3) Total runtime: 1283.111 ms

1)总运行时间:1253.543 ms 2)总运行时间:1250.351 ms 3)总运行时间:1283.111 ms

ORDER  BY p.ordering, p.name

1) Total runtime: 177.266 ms
2) Total runtime: 174.556 ms
3) Total runtime: 177.797 ms

1)总运行时间:177.266 ms 2)总运行时间:174.556 ms 3)总运行时间:177.797 ms

ORDER  BY d.id, p.ordering, p.name

1) Total runtime: 176.628 ms
2) Total runtime: 176.811 ms
3) Total runtime: 178.150 ms
The planner obviously factors in that d.id is functionally dependent.

1)总运行时间:176.628 ms 2)总运行时间:176.811 ms 3)总运行时间:178.150 ms计划器显然是因为d.id在功能上是相关的。

ORDER  BY d.int_col, p.ordering, p.name -- integer column in other table

1) Total runtime: 242.218 ms -- !!
2) Total runtime: 245.234 ms
3) Total runtime: 254.581 ms
The planner obviously misses that d.int_col (NOT NULL) is just as functionally dependent. But sorting by an integer column is cheap.

1)总运行时间:242.218毫秒 - !! 2)总运行时间:245.234 ms 3)总运行时间:254.581 ms规划器显然错过了d.int_col(NOT NULL)与功能相关。但是按整数列排序很便宜。

ORDER  BY p.name -- varchar(255) in same table

1) Total runtime: 2259.171 ms -- !!
2) Total runtime: 2257.650 ms
3) Total runtime: 2258.282 ms
Sorting by a (long) varchar or text column is expensive ...

1)总运行时间:2259.171 ms - !! 2)总运行时间:2257.650 ms 3)总运行时间:2258.282 ms按(长)varchar或text列排序很昂贵...

ORDER  BY p.name COLLATE "C"

1) Total runtime: 327.516 ms -- !!
2) Total runtime: 325.103 ms
3) Total runtime: 327.206 ms
... but not as expensive if done without locale.

1)总运行时间:327.516 ms - !! 2)总运行时间:325.103 ms 3)总运行时间:327.206 ms ...但如果没有语言环境,则不会那么昂贵。

With the locale out of the way, sorting by a varchar column is not quite but almost as fast. Locale "C" is effectively "no locale, just order by byte value". I quote the manual:

如果将语言环境排除在外,则通过varchar列进行排序的速度不是很快,而是几乎一样快。区域设置“C”实际上是“没有区域设置,只是按字节值排序”。我引用手册:

If you want the system to behave as if it had no locale support, use the special locale name C, or equivalently POSIX.

如果您希望系统的行为就像它没有语言环境支持一样,请使用特殊的语言环境名称C或等效的POSIX。

---

Putting it all together, @dvd chose:

总而言之,@ DVD选择:

ORDER  BY d.domain COLLATE "C", p.ordering, p.name

... 3) Total runtime: 275.854 ms
That should do.

... 3)总运行时间:275.854 ms应该这样做。

#3

As far as i can see, you need some indexes

据我所知,你需要一些索引

1. create index product_product_idx01 on product_product(active, site_id); This probably will speed up your query.

在product_product上创建索引product_product_idx01(active,site_id);这可能会加快您的查询速度。

2. why do you order by domain, it's meanless your query

你为什么按域名订购,这对你的查询毫无意义