# Complex view becomes slow when adding ORDER BY in outer query with small LIMIT [closed]

I have a pretty large query in a view (let's call it `a_sql`), that is really fast unless I use `ORDER BY` in an outer `SELECT` with a small `LIMIT`:

``````SELECT
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(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
/* 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
/* 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'
, 'shipped')
OR orders.id IS NULL
``````

Returns around 52.000 rows.

A query of the following type is executed in 12.728 ms:

``````SELECT * FROM A_SQL LIMIT 10
``````

The related `EXPLAIN` output:

``````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)
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)
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)
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
``````

A query of the following type, instead, is executed in 9090.141ms

``````SELECT * FROM a_sql ORDER BY custom_id LIMIT 10
``````

The related `EXPLAIN` output:

``````Limit  (cost=328570.62..328570.64 rows=10 width=324) (actual time=8987.928..8987.929 rows=10 loops=1)
->  Sort  (cost=328570.62..328700.40 rows=51911 width=324) (actual time=8987.926..8987.926 rows=10 loops=1)
Sort Method: top-N heapsort  Memory: 27kB
->  Subquery Scan on x  (cost=306105.20..327448.84 rows=51911 width=324) (actual time=3074.397..8978.470 rows=8004     loops=1)
->  GroupAggregate  (cost=306105.20..326929.73 rows=51911 width=610) (actual time=3074.395..8975.492     rows=8004 loops=1)
->  Sort  (cost=306105.20..306782.04 rows=270739 width=610) (actual time=3073.679..3411.919     rows=467218 loops=1)
Sort Method: external merge  Disk: 56936kB
->  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
->  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
->  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)
->  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)
->  Seq Scan on sizes  (cost=0.00..8642.10 rows=441710 width=8) (actual     time=0.009..119.758 rows=441934 loops=1)
->  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)
->  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)
->  Hash Left Join  (cost=7908.32..17952.45 rows=51831     width=48) (actual time=83.756..251.381 rows=51846     loops=1)
->  Hash Join  (cost=1843.00..10720.93     rows=51831 width=26) (actual     time=27.942..139.640 rows=51846 loops=1)
->  Seq Scan on customs      (cost=0.00..7841.31 rows=51831 width=8)     (actual time=0.009..41.084 rows=51846     loops=1)
->  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
->  Seq Scan on users designers      (cost=0.00..5265.70 rows=63970 width=26)     (actual time=0.003..35.227 rows=63972     loops=1)
->  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
``````

Table definitions are the following. No integrity constraints are defined on the database (using ORM)

``````CREATE TABLE business_orders (
id integer NOT NULL,
user_id integer,
published_at timestamp without time zone,
);
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,
CONSTRAINT customs_pkey PRIMARY KEY (id)
);
ON customs
USING btree

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);
``````

Because the `a_sql` is a view, I can't insert the `ORDER BY` clause inside the view. I will need to call it as a black box.

The use cases for this query are:

• With a limit of 10, ordered by `custom_id`
• With a limit of 10, ordered by `total`
• To filter all rows that have `business_order.user_id = orders.id and business_orders.id = x` (usually not more than 100 rows as result)

The graphical explain of pg_admin, even if I don't understand much, seems to be telling me that if I run the query with no ordering, then the query is using indexes, (and doing "nested loop joins"), while if I do it with the ordering, then it doesn't (it uses "hash joins").

Are there any ways to increase performance?

• After so many rounds of editing the main query in the question is still inconsistent (impossible, copy / paste error?). It doesn't work with `GROUP BY business_orders.id`. You should have pasted what you actually use to begin with. – Erwin Brandstetter Jan 29 '16 at 13:51
• Please don't close (or maybe close, but not delete) this question. Even though the OP didn't get the question right (yet), there are a couple of very interesting aspects here: `join_collapse_limit` , `LEFT` / `RIGHT JOIN`, tricky precedence among joins, conflict of objectives with `WHERE ... ORDER BY ... LIMIT`. – Erwin Brandstetter Jan 29 '16 at 16:50
• @PaulWhite I hope my next questions will be stated better. thanks for the help – Marco Marcianesi Feb 1 '16 at 9:46

Here is what I do in such cases, usually some of this helps:

• Look at the whole query and try to remove unneeded tables from it.

• Rethink outer JOINs (that is, LEFT/RIGHT JOIN) and if possible, eliminate them from view definition, replacing by inner JOINS.

• Try to increase planner constants so the server can put more effort into planning phase. You can do this by increasing `join_collapse_limit` and `from_collapse_limit` to 12, and `geqo_threshold` to 18.

• If you know which plan order is the best, you can lower `join_collapse_limit` to 1 and force proper ordering by explicit JOIN order.

You should read PostgreSQL documentation on Controlling the planner with explicit JOINs and Query Planning Configuration.

## Update

There is one more option to consider:

• Rewrite your query, to isolate the selector part into another subquery, using `WITH selection AS (SELECT DISTINCT ON (business_orders.id) business_orders.id FROM ... ORDER BY business_orders.id LIMIT 10)` but without the aggregates (like count(*),min,max,avg,...). Then use it as a basis for the whole query, where you calculate the aggregates. This way you will avoid costly calculations before grouping.
• I accepted this answer because it had an impact on the execution time. Not fully solving the problem, but reducing it – Marco Marcianesi Jan 26 '16 at 10:15

Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing.

### General difficulty

The Problem is: predicates on some columns, `ORDER BY` on a different column.

In your fast query, without `ORDER BY`, the first (arbitrary) 10 rows can be returned.

In your slow query, with `ORDER BY`, all rows have to be considered and ordered before the top 10 rows can be returned. Such a query can be optimized with a matching index ...

Very similar case:

### Fix query first

Let's get your query straight first. I removed all noise to get an overview and fixed some obvious things:

``````SELECT *  -- includes shoe_model_id - list columns from sub explicitly to exclude it
, sm.id         AS shoe_model_id
, sm.name       AS shoe_model_name
, sm.title      AS shoe_model_title
, mc.id         AS model_category_id
, mc.name       AS model_category_name
, u.email       AS designer_email
, ud.first_name AS designer_first_name
, ud.last_name  AS designer_last_name
FROM (
SELECT sh.shoe_model_id
, SUM(oro.quantity) FILTER (WHERE bo.user_id = o.user_id AND     o.bulk) AS sale_bulk
, SUM(oro.quantity) FILTER (WHERE bo.user_id = o.user_id AND NOT o.bulk) AS sale_not_bulk
, SUM(oro.quantity) FILTER (WHERE bo.user_id = o.user_id)                AS sale_total
, SUM(oro.quantity) FILTER (WHERE bo.user_id <> o.user_id AND     o.bulk) AS buy_bulk
, SUM(oro.quantity) FILTER (WHERE bo.user_id <> o.user_id AND NOT o.bulk) AS buy_not_bulk
, SUM(oro.quantity) FILTER (WHERE bo.user_id <> o.user_id)                AS buy_total
, SUM(oro.quantity) FILTER (WHERE     o.bulk) AS total_bulk
, SUM(oro.quantity) FILTER (WHERE NOT o.bulk) AS total_not_bulk
, COALESCE(SUM(oro.quantity), 0) AS total
, bo.user_id      AS designer_id
, MIN(sh.id)           AS shoe_id
FROM   shoes           sh
JOIN   customs         c   ON c.id = sh.product_id
JOIN   sizes           si  ON si.shoe_id = sh.id
LEFT   JOIN (orders    o
JOIN order_rows oro ON oro.order_id = o.id
AND oro.article_type::text = 'Size'::text  -- data type ???
AND o.state = ANY ('{funded,confirmed,paid, delivered
) ON oro.article_id = si.id
WHERE  sh.product_type = 'Custom'
GROUP  BY bo.id, sh.id
) sub
LEFT   JOIN shoe_models      sm  ON sm.id = sub.shoe_model_id
LEFT   JOIN model_categories mc  ON mc.id = sm.model_category_id;
``````
• Since `sizes` depends on `shoes`, the `LEFT JOIN` on `shoes` is void. Use `[INNER] JOIN` instead.

• Why `oro.article_type::text`? There should not be a cast on the column. What's its data type? Same for `sh.product_type::text`

• You join 10 tables in explicit join syntax. That's more than the default setting for `join_collapse_limit`, which is 8. Hence, it becomes increasingly important that you join in a sensible and correct manner. The query planner is not going to fix your contradictory `FROM` list.

• I run the (updated!) aggregates over relevant tables only and join to the rest later.

• Simplified aggregate expressions - with the new aggregate `FILTER` clause in Postgres 9.4

• More important than performance optimization, I suspect that the calculations in your original query might be incorrect. You include rows in `order_rows` in your sums, even if the respective rows in `orders` do not qualify. I fixed that by joining `orders` and `order_rows` applying your filter before I left-join to the rest (using parentheses).
{"ft_lang_label":"__label__en","ft_lang_prob":0.5455565,"math_prob":0.8323093,"size":18204,"snap":"2021-31-2021-39","text_gpt3_token_len":5591,"char_repetition_ratio":0.20659341,"word_repetition_ratio":0.13405456,"special_character_ratio":0.37793893,"punctuation_ratio":0.23310634,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.97857153,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-07-24T11:09:55Z\",\"WARC-Record-ID\":\"<urn:uuid:056450c8-e1a9-4754-ba13-49954c14268d>\",\"Content-Length\":\"199086\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:108bd4b3-b3e8-4e70-8064-d282ca980ab7>\",\"WARC-Concurrent-To\":\"<urn:uuid:0046455e-2e71-4760-a7d8-82a657a24254>\",\"WARC-IP-Address\":\"151.101.1.69\",\"WARC-Target-URI\":\"https://dba.stackexchange.com/questions/125985/complex-view-becomes-slow-when-adding-order-by-in-outer-query-with-small-limit?noredirect=1\",\"WARC-Payload-Digest\":\"sha1:4DKYWPLABRHW7DHI3PYVMZWJVO7YIO22\",\"WARC-Block-Digest\":\"sha1:OWGZ347DSODNOKGWLFEZTMIBAJBOVNVP\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-31/CC-MAIN-2021-31_segments_1627046150264.90_warc_CC-MAIN-20210724094631-20210724124631-00370.warc.gz\"}"}