Complex view becomes slow when adding ORDER BY in outer query with small LIMIT

Question

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

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

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

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

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?

• Erwin Brandstetter: 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: 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.
• Marco Marcianesi: @PaulWhite I hope my next questions will be stated better. thanks for the help

Answer 1

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.

• Marco Marcianesi: I accepted this answer because it had an impact on the execution time. Not fully solving the problem, but reducing it

Answer 2

Note: This answer addresses several basic problems in the query, but it's not the final solution. The question was still inconsistent after multiple 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:

• Optimize a query with small LIMIT, predicate on one column and order by another

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
 , bo.id AS business_order_id
 , bo.state AS business_order_state
 , bo.published_at AS business_order_published_at
 , MIN(sh.id) AS shoe_id
 FROM shoes sh
 JOIN customs c ON c.id = sh.product_id
 JOIN business_orders bo ON bo.id = c.business_order_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
 ,production,produced,ready_to_ship,shipped}')
 ) ON oro.article_id = si.id
 WHERE sh.product_type = 'Custom'
 GROUP BY bo.id, sh.id
 ) sub
LEFT JOIN users u ON u.id = sub.designer_id
LEFT JOIN user_details ud ON ud.user_id = u.id
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? The cast should not be necessary. 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

• Return counts for multiple ranges in a single SELECT statement

• More importantly 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).