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

Question

Closed. This question needs details or clarity. It is not currently accepting answers.

Want to improve this question? Add details and clarify the problem by editing this post.

Closed 8 years ago.

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?

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, Commented Jan 29, 2016 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, Commented Jan 29, 2016 at 16:50
@PaulWhite I hope my next questions will be stated better. thanks for the help — Marco Marcianesi, Commented Feb 1, 2016 at 9:46

filiprem · Accepted Answer · 2016-01-19 18:32:31Z

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, Commented Jan 26, 2016 at 10:15

Community · Accepted Answer · 2017-04-13 12:43:00Z

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:

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? 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
- Return counts for multiple ranges in a single SELECT statement
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).

Stack Exchange Network

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

2 Answers 2

Update

General difficulty

Fix query first

Not the answer you're looking for? Browse other questions tagged
postgresql
optimization
view
postgresql-9.4
performance
postgresql-performance
or ask your own question.

Linked

Hot Network Questions

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

2 Answers 2

Update

General difficulty

Fix query first

Not the answer you're looking for? Browse other questions tagged postgresqloptimizationviewpostgresql-9.4performancepostgresql-performance or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
postgresql
optimization
view
postgresql-9.4
performance
postgresql-performance
or ask your own question.