Postgresql
在具有較小 LIMIT 的外部查詢中添加 ORDER BY 時，複雜視圖會變慢
我在視圖中有一個非常大的查詢（我們稱之為
a_sql），除非我在一個小ORDER BY的外部使用，否則它真的很快：SELECT``LIMITSELECT customs.id AS custom_id, customs.custom_name AS custom_name, customs.slug AS slug, customs.use_case AS custom_use_case, SUM(CASE WHEN designers.id = orders.user_id AND orders.bulk = 't' THEN order_rows.quantity ELSE 0 END) AS sale_bulk, SUM(CASE WHEN designers.id = orders.user_id AND orders.bulk = 'f' THEN order_rows.quantity ELSE 0 END) AS sale_not_bulk, SUM(CASE WHEN designers.id = orders.user_id THEN order_rows.quantity ELSE 0 END) AS sale_total, SUM(CASE WHEN designers.id <> orders.user_id AND orders.bulk = 't' THEN order_rows.quantity ELSE 0 END) AS buy_bulk, SUM(CASE WHEN designers.id <> orders.user_id AND orders.bulk = 'f' THEN order_rows.quantity ELSE 0 END) AS buy_not_bulk, SUM(CASE WHEN designers.id <> orders.user_id THEN order_rows.quantity ELSE 0 END) AS buy_total, SUM(CASE orders.bulk WHEN 't' THEN order_rows.quantity ELSE 0 END) AS total_bulk, SUM(CASE orders.bulk WHEN 'f' THEN order_rows.quantity ELSE 0 END) AS total_not_bulk, COALESCE(SUM(order_rows.quantity), 0 ) AS total, MIN(shoes.id) AS shoe_id, MIN(shoe_models.id) AS shoe_model_id, MIN(shoe_models.name) AS shoe_model_name, MIN(shoe_models.title) AS shoe_model_title, MIN(model_categories.id) AS model_category_id, MIN(model_categories.name) AS model_category_name, MIN(business_orders.id) AS business_order_id, MIN(business_orders.state) AS business_order_state, MIN(business_orders.published_at) AS business_order_published_at, MIN(designers.id) AS designer_id, MIN(designers.email) AS designer_email, MIN(designer_details.first_name) AS designer_first_name, MIN(designer_details.last_name) AS designer_last_name FROM business_orders /* 10^6 rows */ LEFT JOIN users designers ON designers.id = business_orders.user_id /* 10^6 rows - business_orders has 0 or 1 users, users has n business_orders */ LEFT JOIN user_details designer_details ON designers.id = designer_details.user_id /* 10^6 rows - users has 0 or 1 user_details, user_details has 1 users */ INNER JOIN customs ON business_orders.id = customs.business_order_id /* 10^6 rows - business_orders has 1 customs, customs has 1 business_order */ LEFT JOIN shoes ON shoes.product_id = customs.id AND shoes.product_type = 'Custom' /* 10^6 rows - customs has 1 shoes, shoes has 1 customs */ LEFT JOIN shoe_models ON shoe_models.id = shoes.shoe_model_id /* 10^2 rows - shoes has 1 shoe_models, shoe_models has n shoes */ LEFT JOIN model_categories ON shoe_models.model_category_id = model_categories.id /* 10^1 rows - shoe_models has 1 model_categories, model_categories has n models */ INNER JOIN sizes ON shoes.id = sizes.shoe_id /* 10^6 rows - sizes has 1 shoes, shoes has n sizes */ LEFT JOIN order_rows ON order_rows.article_id = sizes.id AND order_rows.article_type::text = 'Size'::text /* 10^5 rows - sizes has n order_rows, order_rows has 0 or 1 size */ LEFT JOIN orders ON orders.id = order_rows.order_id /* 10^4 rows - order_rows has 1 orders, orders has n order_rows */ WHERE orders.state IN ('funded', 'confirmed', 'paid', 'delivered' ,'production', 'produced', 'ready_to_ship' , 'shipped') OR orders.id IS NULL GROUP BY business_orders.id返回大約 52.000 行。
以下類型的查詢在 12.728 毫秒內執行：
SELECT * FROM A_SQL LIMIT 10相關
EXPLAIN輸出：Limit (cost=3.51..145.53 rows=10 width=324) (actual time=1.545..12.468 rows=10 loops=1) Buffers: shared hit=1652 -> Subquery Scan on x (cost=3.51..737218.84 rows=51911 width=324) (actual time=1.543..12.462 rows=10 loops=1) Buffers: shared hit=1652 -> GroupAggregate (cost=3.51..736699.73 rows=51911 width=610) (actual time=1.542..12.455 rows=10 loops=1) Group Key: business_orders.id Buffers: shared hit=1652 -> Nested Loop Left Join (cost=3.51..716552.04 rows=270739 width=610) (actual time=0.090..4.073 rows=608 loops=1) Filter: (((orders.state)::text = ANY ('{funded,confirmed,paid,delivered,production,produced,ready_to_ship,shipped}'::text[])) OR (orders.id IS NULL)) Rows Removed by Filter: 5 Buffers: shared hit=1652 -> Nested Loop Left Join (cost=3.23..408595.00 rows=448022 width=609) (actual time=0.087..3.264 rows=613 loops=1) Buffers: shared hit=1547 -> Nested Loop (cost=2.94..264656.18 rows=448022 width=605) (actual time=0.082..1.227 rows=596 loops=1) Buffers: shared hit=269 -> Nested Loop Left Join (cost=2.52..130221.18 rows=52594 width=601) (actual time=0.073..0.578 rows=14 loops=1) Buffers: shared hit=197 -> Nested Loop Left Join (cost=2.23..104252.63 rows=51831 width=588) (actual time=0.066..0.478 rows=14 loops=1) Join Filter: (shoe_models.model_category_id = model_categories.id) Rows Removed by Join Filter: 79 Buffers: shared hit=155 -> Nested Loop Left Join (cost=2.23..101141.72 rows=51831 width=72) (actual time=0.055..0.413 rows=14 loops=1) Buffers: shared hit=154 -> Nested Loop (cost=2.09..92396.06 rows=51831 width=52) (actual time=0.051..0.348 rows=14 loops=1) Buffers: shared hit=126 -> Nested Loop Left Join (cost=1.80..65264.56 rows=51831 width=48) (actual time=0.033..0.209 rows=14 loops=1) Buffers: shared hit=84 -> Merge Join (cost=1.38..21836.97 rows=51831 width=26) (actual time=0.022..0.109 rows=14 loops=1) Merge Cond: (business_orders.id = customs.business_order_id) Buffers: shared hit=28 -> Index Scan using business_orders_pkey on business_orders (cost=0.29..3688.80 rows=51911 width=22) (actual time=0.012..0.036 rows=14 loops=1) Buffers: shared hit=14 -> Index Scan using index_customs_on_business_order_id on customs (cost=0.41..17371.39 rows=51831 width=8) (actual time=0.005..0.029 rows=14 loops=1) Buffers: shared hit=14 -> Index Scan using users_pkey on users designers (cost=0.41..0.83 rows=1 width=26) (actual time=0.006..0.006 rows=1 loops=14) Index Cond: (id = business_orders.user_id) Buffers: shared hit=56 -> Index Scan using index_shoes_on_product_id_and_product_type on shoes (cost=0.29..0.51 rows=1 width=12) (actual time=0.007..0.008 rows=1 loops=14) Index Cond: ((product_id = customs.id) AND ((product_type)::text = 'Custom'::text)) Buffers: shared hit=42 -> Index Scan using shoe_models_pkey on shoe_models (cost=0.14..0.16 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=14) Index Cond: (id = shoes.shoe_model_id) Buffers: shared hit=28 -> Materialize (cost=0.00..1.06 rows=4 width=520) (actual time=0.001..0.002 rows=7 loops=14) Buffers: shared hit=1 -> Seq Scan on model_categories (cost=0.00..1.04 rows=4 width=520) (actual time=0.004..0.005 rows=7 loops=1) Buffers: shared hit=1 -> Index Scan using index_user_details_on_user_id on user_details designer_details (cost=0.29..0.49 rows=1 width=17) (actual time=0.006..0.006 rows=1 loops=14) Index Cond: (designers.id = user_id) Buffers: shared hit=42 -> Index Scan using index_sizes_on_shoe_id on sizes (cost=0.42..2.00 rows=56 width=8) (actual time=0.006..0.030 rows=43 loops=14) Index Cond: (shoe_id = shoes.id) Buffers: shared hit=72 -> Index Scan using index_order_rows_on_article_id on order_rows (cost=0.29..0.31 rows=1 width=12) (actual time=0.003..0.003 rows=0 loops=596) Index Cond: (article_id = sizes.id) Filter: ((article_type)::text = 'Size'::text) Rows Removed by Filter: 2 Buffers: shared hit=1278 -> Index Scan using orders_pkey on orders (cost=0.29..0.67 rows=1 width=18) (actual time=0.000..0.000 rows=0 loops=613) Index Cond: (id = order_rows.order_id) Buffers: shared hit=105 Planning time: 5.013 ms Execution time: 12.728 ms相反，以下類型的查詢在 9090.141 毫秒內執行
SELECT * FROM a_sql ORDER BY custom_id LIMIT 10相關
EXPLAIN輸出：Limit (cost=328570.62..328570.64 rows=10 width=324) (actual time=8987.928..8987.929 rows=10 loops=1) Buffers: shared hit=10412 read=12400, temp read=18319 written=18063 -> Sort (cost=328570.62..328700.40 rows=51911 width=324) (actual time=8987.926..8987.926 rows=10 loops=1) Sort Key: x.business_order_id Sort Method: top-N heapsort Memory: 27kB Buffers: shared hit=10412 read=12400, temp read=18319 written=18063 -> Subquery Scan on x (cost=306105.20..327448.84 rows=51911 width=324) (actual time=3074.397..8978.470 rows=8004 loops=1) Buffers: shared hit=10412 read=12400, temp read=18319 written=18063 -> GroupAggregate (cost=306105.20..326929.73 rows=51911 width=610) (actual time=3074.395..8975.492 rows=8004 loops=1) Group Key: business_orders.id Buffers: shared hit=10412 read=12400, temp read=18319 written=18063 -> Sort (cost=306105.20..306782.04 rows=270739 width=610) (actual time=3073.679..3411.919 rows=467218 loops=1) Sort Key: business_orders.id Sort Method: external merge Disk: 56936kB Buffers: shared hit=10412 read=12400, temp read=18319 written=18063 -> Hash Right Join (cost=98065.48..133611.68 rows=270739 width=610) (actual time=1559.328..2325.275 rows=467218 loops=1) Hash Cond: (order_rows.article_id = sizes.id) Filter: (((orders.state)::text = ANY ('{funded,confirmed,paid,delivered,production,produced,ready_to_ship,shipped}'::text[])) OR (orders.id IS NULL)) Rows Removed by Filter: 3712 Buffers: shared hit=10412 read=12400, temp read=9442 written=9186 -> Hash Left Join (cost=813.00..1497.05 rows=7367 width=26) (actual time=9.566..22.691 rows=7367 loops=1) Hash Cond: (order_rows.order_id = orders.id) Buffers: shared hit=888 -> Seq Scan on order_rows (cost=0.00..509.08 rows=7367 width=12) (actual time=0.029..5.732 rows=7367 loops=1) Filter: ((article_type)::text = 'Size'::text) Rows Removed by Filter: 11199 Buffers: shared hit=277 -> Hash (cost=700.78..700.78 rows=8978 width=18) (actual time=9.507..9.507 rows=8993 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 470kB Buffers: shared hit=611 -> Seq Scan on orders (cost=0.00..700.78 rows=8978 width=18) (actual time=0.009..7.142 rows=8993 loops=1) Buffers: shared hit=611 -> Hash (cost=57087.20..57087.20 rows=448022 width=605) (actual time=1547.263..1547.263 rows=469413 loops=1) Buckets: 1024 Batches: 128 Memory Usage: 567kB Buffers: shared hit=9524 read=12400, temp read=1037 written=8932 -> Hash Left Join (cost=30955.54..57087.20 rows=448022 width=605) (actual time=496.442..1160.554 rows=469413 loops=1) Hash Cond: (shoes.shoe_model_id = shoe_models.id) Buffers: shared hit=9524 read=12400, temp read=1037 written=1035 -> Hash Join (cost=30938.67..52547.10 rows=448022 width=69) (actual time=496.300..964.720 rows=469413 loops=1) Hash Cond: (sizes.shoe_id = shoes.id) Buffers: shared hit=9509 read=12400, temp read=1037 written=1035 -> Seq Scan on sizes (cost=0.00..8642.10 rows=441710 width=8) (actual time=0.009..119.758 rows=441934 loops=1) Buffers: shared hit=797 read=3428 -> Hash (cost=29664.25..29664.25 rows=52594 width=65) (actual time=496.056..496.056 rows=54329 loops=1) Buckets: 4096 Batches: 2 Memory Usage: 2679kB Buffers: shared hit=8712 read=8972, temp written=294 -> Hash Left Join (cost=15725.17..29664.25 rows=52594 width=65) (actual time=162.077..460.095 rows=54329 loops=1) Hash Cond: (designers.id = designer_details.user_id) Buffers: shared hit=8712 read=8972 -> Hash Join (cost=11607.65..22688.39 rows=51831 width=52) (actual time=124.442..362.315 rows=51846 loops=1) Hash Cond: (customs.id = shoes.product_id) Buffers: shared hit=6055 read=8972 -> Hash Left Join (cost=7908.32..17952.45 rows=51831 width=48) (actual time=83.756..251.381 rows=51846 loops=1) Hash Cond: (business_orders.user_id = designers.id) Buffers: shared hit=3652 read=8972 -> Hash Join (cost=1843.00..10720.93 rows=51831 width=26) (actual time=27.942..139.640 rows=51846 loops=1) Hash Cond: (customs.business_order_id = business_orders.id) Buffers: shared hit=3079 read=4919 -> Seq Scan on customs (cost=0.00..7841.31 rows=51831 width=8) (actual time=0.009..41.084 rows=51846 loops=1) Buffers: shared hit=2404 read=4919 -> Hash (cost=1194.11..1194.11 rows=51911 width=22) (actual time=27.888..27.888 rows=51849 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 2513kB Buffers: shared hit=675 -> Seq Scan on business_orders (cost=0.00..1194.11 rows=51911 width=22) (actual time=0.007..15.422 rows=51849 loops=1) Buffers: shared hit=675 -> Hash (cost=5265.70..5265.70 rows=63970 width=26) (actual time=55.788..55.788 rows=63972 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 3679kB Buffers: shared hit=573 read=4053 -> Seq Scan on users designers (cost=0.00..5265.70 rows=63970 width=26) (actual time=0.003..35.227 rows=63972 loops=1) Buffers: shared hit=573 read=4053 -> Hash (cost=3051.16..3051.16 rows=51853 width=12) (actual time=40.654..40.654 rows=51846 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 2154kB Buffers: shared hit=2403 -> Seq Scan on shoes (cost=0.00..3051.16 rows=51853 width=12) (actual time=0.009..28.311 rows=51846 loops=1) Filter: ((product_type)::text = 'Custom'::text) Buffers: shared hit=2403 -> Hash (cost=3306.12..3306.12 rows=64912 width=17) (actual time=37.610..37.610 rows=64670 loops=1) Buckets: 8192 Batches: 1 Memory Usage: 2748kB Buffers: shared hit=2657 -> Seq Scan on user_details designer_details (cost=0.00..3306.12 rows=64912 width=17) (actual time=0.007..19.790 rows=64670 loops=1) Buffers: shared hit=2657 -> Hash (cost=16.19..16.19 rows=54 width=540) (actual time=0.121..0.121 rows=54 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 4kB Buffers: shared hit=15 -> Hash Left Join (cost=1.09..16.19 rows=54 width=540) (actual time=0.034..0.101 rows=54 loops=1) Hash Cond: (shoe_models.model_category_id = model_categories.id) Buffers: shared hit=15 -> Seq Scan on shoe_models (cost=0.00..14.54 rows=54 width=24) (actual time=0.006..0.028 rows=54 loops=1) Buffers: shared hit=14 -> Hash (cost=1.04..1.04 rows=4 width=520) (actual time=0.016..0.016 rows=7 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB Buffers: shared hit=1 -> Seq Scan on model_categories (cost=0.00..1.04 rows=4 width=520) (actual time=0.006..0.012 rows=7 loops=1) Buffers: shared hit=1 Planning time: 4.263 ms Execution time: 9090.141 ms表定義如下。沒有在數據庫上定義完整性約束（使用 ORM）
CREATE TABLE business_orders ( id integer NOT NULL, user_id integer, published_at timestamp without time zone, CONSTRAINT business_orders_pkey PRIMARY KEY (id) ); CREATE INDEX index_business_orders_on_user_id ON business_orders USING btree (user_id); CREATE TABLE users ( id serial NOT NULL,, email character varying(255) NOT NULL DEFAULT ''::character varying, CONSTRAINT users_pkey PRIMARY KEY (id) ); CREATE UNIQUE INDEX index_users_on_email ON users USING btree (email COLLATE pg_catalog."default"); CREATE TABLE user_details ( id serial NOT NULL, user_id integer, first_name character varying(255), last_name character varying(255), CONSTRAINT user_details_pkey PRIMARY KEY (id) ); CREATE INDEX index_user_details_on_user_id ON user_details USING btree (user_id); CREATE TABLE customs ( id serial NOT NULL, shoes_assortment_id integer, business_order_id integer, CONSTRAINT customs_pkey PRIMARY KEY (id) ); CREATE INDEX index_customs_on_business_order_id ON customs USING btree (business_order_id); CREATE TABLE shoes ( id serial NOT NULL, product_id integer, product_type character varying(255), CONSTRAINT shoes_pkey PRIMARY KEY (id) ); CREATE INDEX index_shoes_on_product_id_and_product_type ON shoes USING btree (product_id, product_type COLLATE pg_catalog."default"); CREATE INDEX index_shoes_on_shoe_model_id ON shoes USING btree (shoe_model_id); CREATE TABLE shoe_models ( id serial NOT NULL, name character varying(255) NOT NULL, title character varying(255), model_category_id integer, CONSTRAINT shoe_models_pkey PRIMARY KEY (id) ); CREATE INDEX index_shoe_models_on_model_category_id ON shoe_models USING btree (model_category_id); CREATE UNIQUE INDEX index_shoe_models_on_name ON shoe_models USING btree (name COLLATE pg_catalog."default"); CREATE TABLE model_categories ( id serial NOT NULL, name character varying(255) NOT NULL, sort_order integer, created_at timestamp without time zone NOT NULL, updated_at timestamp without time zone NOT NULL, access_level integer, CONSTRAINT model_categories_pkey PRIMARY KEY (id) ); CREATE UNIQUE INDEX index_model_categories_on_name ON model_categories USING btree (name COLLATE pg_catalog."default"); CREATE TABLE sizes ( id serial NOT NULL, shoe_id integer, CONSTRAINT sizes_pkey PRIMARY KEY (id) ); CREATE INDEX index_sizes_on_shoe_id ON sizes USING btree (shoe_id); CREATE TABLE order_rows ( id serial NOT NULL, order_id integer, quantity integer, article_id integer, article_type character varying(255), article_name character varying(255), unit_taxed_cents integer, CONSTRAINT order_rows_pkey PRIMARY KEY (id) ); CREATE INDEX index_order_rows_on_article_id ON order_rows USING btree (article_id); CREATE INDEX index_order_rows_on_article_type ON order_rows USING btree (article_type COLLATE pg_catalog."default"); CREATE INDEX index_order_rows_on_order_id ON order_rows USING btree (order_id); CREATE INDEX index_order_rows_on_quantity ON order_rows USING btree (quantity); CREATE INDEX index_order_rows_on_unit_taxed_cents ON order_rows USING btree (unit_taxed_cents); CREATE TABLE orders ( id serial NOT NULL, user_id integer, state character varying(255), bulk boolean DEFAULT false, CONSTRAINT orders_pkey PRIMARY KEY (id) ); CREATE INDEX index_orders_on_user_id ON orders USING btree (user_id);因為
a_sql是一個視圖，所以我不能ORDER BY在視圖內插入子句。我需要將其稱為黑匣子。此查詢的案例是：
- 限 10 個，按以下順序訂購
custom_id- 限 10 個，按以下順序訂購
total- 過濾所有具有
business_order.user_id = orders.id and business_orders.id = x（通常不超過 100 行作為結果）的行pg_admin 的圖形解釋，即使我不太了解，似乎在告訴我，如果我在沒有排序的情況下執行查詢，那麼查詢正在使用索引（並執行“嵌套循環連接”），而如果我按順序執行，然後不執行（它使用“散列連接”）。
有什麼方法可以提高性能嗎？
這是我在這種情況下所做的，通常其中一些會有所幫助：
- 查看整個查詢並嘗試從中刪除不需要的表。
- 重新考慮外部 JOIN（即 LEFT/RIGHT JOIN），如果可能，從視圖定義中消除它們，代之以內部 JOIN。
- 嘗試增加規劃器常量，以便伺服器可以在規劃階段投入更多精力。您可以通過將
join_collapse_limit和from_collapse_limit增加到 12 和geqo_threshold18 來做到這一點。- 如果您知道哪個計劃順序最好，您可以降低
join_collapse_limit到 1 並通過顯式 JOIN 順序強制正確排序。您應該閱讀 PostgreSQL 文件中的Controlling the planner with explicit JOINs和Query Planning Configuration。
更新
還有一種選擇需要考慮：
- 重寫您的查詢，將選擇器部分隔離到另一個子查詢中，使用
WITH selection AS (SELECT DISTINCT ON (business_orders.id) business_orders.id FROM ... ORDER BY business_orders.id LIMIT 10)但不使用聚合（如 count(*)、min、max、avg、…）。然後將其用作整個查詢的基礎，您可以在其中計算聚合。這樣，您將避免在分組之前進行昂貴的計算。