0

PostgresSQL - Join and get distinct left table rows only without gigantic subquery sort

We have a midsized database that has 50k rows of products and 33m rows of product prices at various store locations.

We join the products table to the access table with various where conditions / order by the suit the end user the best.

A simple explanation of a typical query would be 100 distinct products, sorted by highest price first, sorted by in stock first.

Seems simple enough... Everything I read online says to join the tables with a LEFT INNER JOIN (perhaps this is the wrong type of join to get unique rows?) on products_displayproductaccess in a sub query, then order the outer query by the order we're looking for. Problem is the performance is brutal... We partitioned the tables and it did help, but not significantly due the large sort size.

I'm surprised this is such an issue in Postgres (or perhaps other DB's as well) or the solution has just alluded me and the other upwork DBA's we've paid. It's just a join to get unique rows from the left table...

products_displayproduct

Table "public.products_displayproduct"
        Column         |           Type           | Collation | Nullable |                       Default                       | Storage  | Stats target | Description 
-----------------------+--------------------------+-----------+----------+-----------------------------------------------------+----------+--------------+-------------
 id                    | integer                  |           | not null | nextval('products_displayproduct_id_seq'::regclass) | plain    |              | 
 date_created          | timestamp with time zone |           | not null |                                                     | plain    |              | 
 date_updated          | timestamp with time zone |           | not null |                                                     | plain    |              | 
 name                  | character varying(255)   |           | not null |                                                     | extended |              | 
 sub_title             | character varying(255)   |           |          |                                                     | extended |              | 
 tags                  | character varying(255)   |           |          |                                                     | extended |              | 
 has_multiple_variants | boolean                  |           | not null |                                                     | plain    |              | 
 rating                | numeric(3,2)             |           | not null |                                                     | main     |              | 
 reviews               | integer                  |           | not null |                                                     | plain    |              | 
 is_toppick            | boolean                  |           | not null |                                                     | plain    |              | 
 search_index          | tsvector                 |           | not null |                                                     | extended |              | 
 brand_id              | integer                  |           |          |                                                     | plain    |              | 
 placement_id          | integer                  |           | not null |                                                     | plain    |              | 
 poster_image_id       | integer                  |           |          |                                                     | plain    |              | 
 last_amalgamation     | timestamp with time zone |           |          |                                                     | plain    |              | 
Indexes:
    "products_displayproduct_pkey" PRIMARY KEY, btree (id)
    "products_di_id_f3063a_idx" btree (id, placement_id)
    "products_di_search__a06b26_gist" gist (search_index)
    "products_displayproduct_brand_id_c215ec11" btree (brand_id)
    "products_displayproduct_is_toppick_3901660d" btree (is_toppick)
    "products_displayproduct_last_amalgamation_bcfdab2f" btree (last_amalgamation)
    "products_displayproduct_name_6e5686c5" btree (name)
    "products_displayproduct_name_6e5686c5_like" btree (name varchar_pattern_ops)
    "products_displayproduct_name_gist" gist (name gist_trgm_ops)
    "products_displayproduct_placement_id_90f0d4a2" btree (placement_id)
    "products_displayproduct_poster_image_id_7c188e4d" btree (poster_image_id)
    "products_displayproduct_rating_98794c10" btree (rating)
    "products_displayproduct_reviews_4d5513bb" btree (reviews)
    "products_displayproduct_sub_title_72354226" btree (sub_title)
    "products_displayproduct_sub_title_72354226_like" btree (sub_title varchar_pattern_ops)
    "products_displayproduct_tags_ab900c6b" btree (tags)
    "products_displayproduct_tags_ab900c6b_like" btree (tags varchar_pattern_ops)
Foreign-key constraints:
    "products_displayprod_brand_id_c215ec11_fk_products_" FOREIGN KEY (brand_id) REFERENCES products_displaybrand(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_placement_id_90f0d4a2_fk_retailers" FOREIGN KEY (placement_id) REFERENCES retailers_displaysubcategory(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_poster_image_id_7c188e4d_fk_products_" FOREIGN KEY (poster_image_id) REFERENCES products_displayproductimage(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "intake_intakeproduct" CONSTRAINT "intake_intakeproduct_displayed_as_id_479d7ed0_fk_products_" FOREIGN KEY (displayed_as_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "products_amalgamateddisplayproduct" CONSTRAINT "products_amalgamated_orig_product_id_df68bcd8_fk_products_" FOREIGN KEY (orig_product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "products_displayproductvariant" CONSTRAINT "products_displayprod_product_id_210696e5_fk_products_" FOREIGN KEY (product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "products_displayproductaccess" CONSTRAINT "products_displayprod_product_id_2a2032ac_fk_products_" FOREIGN KEY (product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "reviews_displayproductreview" CONSTRAINT "reviews_displayprodu_product_id_1d59703e_fk_products_" FOREIGN KEY (product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "search_searchquery" CONSTRAINT "search_searchquery_from_product_id_8f9dfd22_fk_products_" FOREIGN KEY (from_product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED

products_displayproductaccess

       Column       |           Type           | Collation | Nullable |                          Default                          | Storage  | Stats target | Description 
--------------------+--------------------------+-----------+----------+-----------------------------------------------------------+----------+--------------+-------------
 id                 | integer                  |           | not null | nextval('products_displayproductaccess_id_seq'::regclass) | plain    |              | 
 date_created       | timestamp with time zone |           | not null |                                                           | plain    |              | 
 date_updated       | timestamp with time zone |           | not null |                                                           | plain    |              | 
 price              | numeric(16,2)            |           | not null |                                                           | main     |              | 
 quantity           | smallint                 |           | not null |                                                           | plain    |              | 
 department         | character varying(64)    |           |          |                                                           | extended |              | 
 aisle              | character varying(64)    |           |          |                                                           | extended |              | 
 original_price     | numeric(16,2)            |           |          |                                                           | main     |              | 
 is_onsale          | boolean                  |           | not null |                                                           | plain    |              | 
 is_instock         | boolean                  |           | not null |                                                           | plain    |              | 
 sale_until         | date                     |           |          |                                                           | plain    |              | 
 sale_percent       | numeric(5,2)             |           | not null |                                                           | main     |              | 
 sale_amount        | numeric(16,2)            |           | not null |                                                           | main     |              | 
 location_id        | integer                  |           | not null |                                                           | plain    |              | 
 product_variant_id | integer                  |           | not null |                                                           | plain    |              | 
 retailer_id        | integer                  |           | not null |                                                           | plain    |              | 
 product_id         | integer                  |           |          |                                                           | plain    |              | 
 location_intspace  | integer                  |           | not null |                                                           | plain    |              | 
Indexes:
    "products_displayproductaccess_pkey" PRIMARY KEY, btree (id)
    "pmtest" btree (location_intspace, location_id) INCLUDE (product_id, price, is_instock)
    "products_displayproductaccess_aisle_61457682" btree (aisle)
    "products_displayproductaccess_aisle_61457682_like" btree (aisle varchar_pattern_ops)
    "products_displayproductaccess_date_created_d38d9b4e" btree (date_created)
    "products_displayproductaccess_date_updated_0c689801" btree (date_updated)
    "products_displayproductaccess_department_a7031553" btree (department)
    "products_displayproductaccess_department_a7031553_like" btree (department varchar_pattern_ops)
    "products_displayproductaccess_idx_highest_priced" btree (product_id, price DESC, is_instock DESC)
    "products_displayproductaccess_is_instock_d5c2eef0" btree (is_instock)
    "products_displayproductaccess_is_onsale_bdc85585" btree (is_onsale)
    "products_displayproductaccess_location_id_fe5aeb43" btree (location_id)
    "products_displayproductaccess_original_price_904da60f" btree (original_price)
    "products_displayproductaccess_price_0e2f3286" btree (price)
    "products_displayproductaccess_product_id_2a2032ac" btree (product_id)
    "products_displayproductaccess_product_variant_id_ab7b9896" btree (product_variant_id)
    "products_displayproductaccess_quantity_72b1a17c" btree (quantity)
    "products_displayproductaccess_retailer_id_70041027" btree (retailer_id)
    "products_displayproductaccess_sale_amount_6cf01b14" btree (sale_amount)
    "products_displayproductaccess_sale_percent_5f469f10" btree (sale_percent)
    "products_displayproductaccess_sale_until_afb569bf" btree (sale_until)
Foreign-key constraints:
    "products_displayprod_location_id_fe5aeb43_fk_retailers" FOREIGN KEY (location_id) REFERENCES retailers_baselocation(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_product_id_2a2032ac_fk_products_" FOREIGN KEY (product_id) REFERENCES products_displayproduct(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_product_variant_id_ab7b9896_fk_products_" FOREIGN KEY (product_variant_id) REFERENCES products_displayproductvariant(id) DEFERRABLE INITIALLY DEFERRED
    "products_displayprod_retailer_id_70041027_fk_retailers" FOREIGN KEY (retailer_id) REFERENCES retailers_retailer(id) DEFERRABLE INITIALLY DEFERRED
Triggers:
    after_insert_products_displayproductaccess_trigger AFTER INSERT ON products_displayproductaccess FOR EACH ROW EXECUTE PROCEDURE products_displayproductaccess_delete_master()
    before_insert_products_displayproductaccess_trigger BEFORE INSERT ON products_displayproductaccess FOR EACH ROW EXECUTE PROCEDURE products_displayproductaccess_insert_child()
Child tables: products_displayproductaccess_745_745,
              products_displayproductaccess_746_746,
              products_displayproductaccess_747_747,
              products_displayproductaccess_748_748,
              products_displayproductaccess_749_749,
              products_displayproductaccess_750_750,
              products_displayproductaccess_751_751,
              products_displayproductaccess_752_752,
              products_displayproductaccess_753_753,
              products_displayproductaccess_754_754,
              products_displayproductaccess_755_755,
              products_displayproductaccess_756_756,
              products_displayproductaccess_757_757,
              products_displayproductaccess_758_758,
              products_displayproductaccess_759_759,
              products_displayproductaccess_760_760,
              products_displayproductaccess_761_761,
              products_displayproductaccess_762_762,
              products_displayproductaccess_763_763,
              products_displayproductaccess_764_764,
              products_displayproductaccess_765_765,
              products_displayproductaccess_766_766,
              products_displayproductaccess_767_767,
              products_displayproductaccess_768_768,
              products_displayproductaccess_769_769,
              products_displayproductaccess_770_770,
              products_displayproductaccess_771_771,
              products_displayproductaccess_772_772,
              products_displayproductaccess_773_773,
              products_displayproductaccess_774_774,
              products_displayproductaccess_775_775,
              products_displayproductaccess_776_776,
              products_displayproductaccess_777_777,
              products_displayproductaccess_778_778,
              products_displayproductaccess_779_779,
              products_displayproductaccess_780_780,
              products_displayproductaccess_781_781,
              products_displayproductaccess_782_782,
              products_displayproductaccess_783_783,
              products_displayproductaccess_784_784,
              products_displayproductaccess_785_785,
              products_displayproductaccess_786_786,
              products_displayproductaccess_787_787,
              products_displayproductaccess_788_788,
              products_displayproductaccess_789_789,
              products_displayproductaccess_790_790,
              products_displayproductaccess_791_791,
              products_displayproductaccess_792_792,
              products_displayproductaccess_793_793,
              products_displayproductaccess_794_794,
              products_displayproductaccess_795_795,
              products_displayproductaccess_796_796,
              products_displayproductaccess_797_797,
              products_displayproductaccess_798_798,
              products_displayproductaccess_799_799,
              products_displayproductaccess_801_801,
              products_displayproductaccess_802_802,
              products_displayproductaccess_803_803,
              products_displayproductaccess_805_805,
              products_displayproductaccess_806_806,
              products_displayproductaccess_807_807,
              products_displayproductaccess_808_808,
              products_displayproductaccess_809_809,
              products_displayproductaccess_810_810,
              products_displayproductaccess_811_811,
              products_displayproductaccess_812_812,
              products_displayproductaccess_814_814,
              products_displayproductaccess_816_816,
              products_displayproductaccess_818_818,
              products_displayproductaccess_819_819,
              products_displayproductaccess_821_821,
              products_displayproductaccess_822_822,
              products_displayproductaccess_824_824,
              products_displayproductaccess_827_827,
              products_displayproductaccess_837_837,
              products_displayproductaccess_849_849,
              products_displayproductaccess_859_859

Attempt #1 (right results - wrong execution time) - using subquery and distinct. The performance is brutal because postgres will find all the matching rows from the index first, then do a sort on those entries which is can be millions of rows

SELECT * FROM (
    SELECT 
      DISTINCT ON (
          "products_displayproductaccess"."product_id"
      ) 
      "products_displayproduct"."id", 
      "products_displayproduct"."date_created", 
      "products_displayproduct"."date_updated", 
      "products_displayproduct"."name", 
      "products_displayproduct"."sub_title", 
      "products_displayproduct"."tags", 
      "products_displayproduct"."has_multiple_variants", 
      "products_displayproduct"."placement_id", 
      "products_displayproduct"."brand_id", 
      "products_displayproduct"."poster_image_id", 
      "products_displayproduct"."rating", 
      "products_displayproduct"."reviews", 
      "products_displayproduct"."is_toppick", 
      "products_displayproduct"."last_amalgamation", 
      "products_displayproduct"."search_index",
      "products_displayproductaccess"."price",
      "products_displayproductaccess"."is_instock"
    FROM 
      "products_displayproduct" 
      INNER JOIN "products_displayproductaccess" ON (
        "products_displayproduct"."id" = "products_displayproductaccess"."product_id"
      ) 
    WHERE 
      (
        --"products_displayproduct"."placement_id" = 853
        "products_displayproductaccess"."location_intspace" IN (755)
        AND "products_displayproductaccess"."location_id" IN (
         55,
         60,
         61,
         62,
         64,
         65,
         68,
         69,
         72,
         78,
         79,
         81,
         84,
         85,
         89,
         92,
         97,
         99,
         101,
         102,
         113,
         120,
         121,
         130,
         149,
         187,
         210,
         234,
         597,
         599,
         601,
         602,
         603,
         605,
         606,
         607,
         608,
         610,
         611,
         613,
         632,
         633,
         634,
         635,
         636,
         637,
         865,
         870,
         900,
         910,
         950,
         951,
         952,
         953,
         954,
         955,
         999,
         1045,
         1046,
         1521,
         1522,
         1525,
         1526,
         1531,
         1532
        )
      ) 
    ORDER BY 
      "products_displayproductaccess"."product_id" ASC NULLS LAST,
      "products_displayproductaccess"."price" DESC NULLS FIRST,
      "products_displayproductaccess"."is_instock" DESC NULLS FIRST
) as subquery
ORDER BY subquery.price DESC NULLS FIRST, subquery.is_instock DESC NULLS FIRST
LIMIT 100

RESULTS - Subquery + Distinct

"Limit  (cost=272865.00..272865.25 rows=100 width=719) (actual time=1087.171..1087.184 rows=100 loops=1)"
"  ->  Sort  (cost=272865.00..272936.34 rows=28534 width=719) (actual time=1087.170..1087.175 rows=100 loops=1)"
"        Sort Key: subquery.price DESC, subquery.is_instock DESC"
"        Sort Method: top-N heapsort  Memory: 95kB"
"        ->  Subquery Scan on subquery  (cost=23.32..271774.45 rows=28534 width=719) (actual time=0.031..1067.073 rows=51147 loops=1)"
"              ->  Unique  (cost=23.32..271489.11 rows=28534 width=723) (actual time=0.029..1057.363 rows=51147 loops=1)"
"                    ->  Merge Join  (cost=23.32..266823.74 rows=1866147 width=723) (actual time=0.029..976.558 rows=1866146 loops=1)"
"                          Merge Cond: (products_displayproductaccess.product_id = products_displayproduct.id)"
"                          ->  Merge Append  (cost=0.56..240512.03 rows=1866147 width=11) (actual time=0.016..714.280 rows=1866146 loops=1)"
"                                Sort Key: products_displayproductaccess.product_id, products_displayproductaccess.price DESC, products_displayproductaccess.is_instock DESC"
"                                ->  Index Scan using products_displayproductaccess_idx_highest_priced on products_displayproductaccess  (cost=0.12..2.29 rows=1 width=11) (actual time=0.003..0.003 rows=0 loops=1)"
"                                      Filter: ((location_intspace = 755) AND (location_id = ANY ('{55,60,61,62,64,65,68,69,72,78,79,81,84,85,89,92,97,99,101,102,113,120,121,130,149,187,210,234,597,599,601,602,603,605,606,607,608,610,611,613,632,633,634,635,636,637,865,870,900,910,950,951,952,953,954,955,999,1045,1046,1521,1522,1525,1526,1531,1532}'::integer[])))"
"                                ->  Index Scan using test_1_idx on products_displayproductaccess_755_755  (cost=0.43..221848.26 rows=1866146 width=11) (actual time=0.013..627.617 rows=1866146 loops=1)"
"                                      Filter: ((location_intspace = 755) AND (location_id = ANY ('{55,60,61,62,64,65,68,69,72,78,79,81,84,85,89,92,97,99,101,102,113,120,121,130,149,187,210,234,597,599,601,602,603,605,606,607,608,610,611,613,632,633,634,635,636,637,865,870,900,910,950,951,952,953,954,955,999,1045,1046,1521,1522,1525,1526,1531,1532}'::integer[])))"
"                          ->  Index Scan using products_displayproduct_pkey on products_displayproduct  (cost=0.29..2877.23 rows=52045 width=712) (actual time=0.009..36.709 rows=52045 loops=1)"
"Planning Time: 17.100 ms"
"Execution Time: 1087.231 ms"

Attempt #2 (wrong results - excellent execution time) - no subquery with distinct. This is very close to the performance and results we're hoping for. Postgres can simply walk the combined index because it's in the exact same order as the order by statement. The problem is there will be duplicate rows when the price on the products_displayproductaccess table varies for example. I simply don't know how to wrap this in say a function that can take 50 rows at a time squash the duplicates and take another 50 until it satisfies the limit and offset properly.

    SELECT 
      DISTINCT ON (
          "products_displayproductaccess"."price",
          "products_displayproductaccess"."is_instock",
          "products_displayproductaccess"."product_id"
        ) 
      "products_displayproduct"."id", 
      "products_displayproduct"."date_created", 
      "products_displayproduct"."date_updated", 
      "products_displayproduct"."name", 
      "products_displayproduct"."sub_title", 
      "products_displayproduct"."tags", 
      "products_displayproduct"."has_multiple_variants", 
      "products_displayproduct"."placement_id", 
      "products_displayproduct"."brand_id", 
      "products_displayproduct"."poster_image_id", 
      "products_displayproduct"."rating", 
      "products_displayproduct"."reviews", 
      "products_displayproduct"."is_toppick", 
      "products_displayproduct"."last_amalgamation", 
      "products_displayproduct"."search_index" 
    FROM 
      "products_displayproduct" 
      INNER JOIN "products_displayproductaccess" ON (
        "products_displayproduct"."id" = "products_displayproductaccess"."product_id"
      ) 
    WHERE 
      (
        --"products_displayproduct"."placement_id" = 853
        "products_displayproductaccess"."location_intspace" IN (755) 
        AND "products_displayproductaccess"."location_id" IN (
         55,
         60,
         61,
         62,
         64,
         65,
         68,
         69,
         72,
         78,
         79,
         81,
         84,
         85,
         89,
         92,
         97,
         99,
         101,
         102,
         113,
         120,
         121,
         130,
         149,
         187,
         210,
         234,
         597,
         599,
         601,
         602,
         603,
         605,
         606,
         607,
         608,
         610,
         611,
         613,
         632,
         633,
         634,
         635,
         636,
         637,
         865,
         870,
         900,
         910,
         950,
         951,
         952,
         953,
         954,
         955,
         999,
         1045,
         1046,
         1521,
         1522,
         1525,
         1526,
         1531,
         1532
        )
      ) 
    ORDER BY 
      "products_displayproductaccess"."price" DESC NULLS FIRST,
      "products_displayproductaccess"."is_instock" DESC NULLS FIRST,
      "products_displayproductaccess"."product_id" ASC NULLS LAST
LIMIT 100

RESULTS - Walking the Index

"Limit  (cost=2.77..225.27 rows=100 width=723) (actual time=0.069..2.854 rows=100 loops=1)"
"  ->  Unique  (cost=2.77..830440.99 rows=373230 width=723) (actual time=0.069..2.845 rows=100 loops=1)"
"        ->  Nested Loop  (cost=2.77..816444.89 rows=1866147 width=723) (actual time=0.067..2.647 rows=1043 loops=1)"
"              ->  Merge Append  (cost=2.48..240507.76 rows=1866147 width=11) (actual time=0.055..1.105 rows=1043 loops=1)"
"                    Sort Key: products_displayproductaccess.price DESC, products_displayproductaccess.is_instock DESC, products_displayproductaccess.product_id"
"                    ->  Sort  (cost=2.04..2.05 rows=1 width=11) (actual time=0.005..0.006 rows=0 loops=1)"
"                          Sort Key: products_displayproductaccess.price DESC, products_displayproductaccess.is_instock DESC, products_displayproductaccess.product_id"
"                          Sort Method: quicksort  Memory: 25kB"
"                          ->  Index Scan using products_displayproductaccess_product_id_2a2032ac on products_displayproductaccess  (cost=0.12..2.03 rows=1 width=11) (actual time=0.003..0.003 rows=0 loops=1)"
"                                Filter: ((location_intspace = 755) AND (location_id = ANY ('{55,60,61,62,64,65,68,69,72,78,79,81,84,85,89,92,97,99,101,102,113,120,121,130,149,187,210,234,597,599,601,602,603,605,606,607,608,610,611,613,632,633,634,635,636,637,865,870,900,910,950,951,952,953,954,955,999,1045,1046,1521,1522,1525,1526,1531,1532}'::integer[])))"
"                    ->  Index Scan using test_2_idx on products_displayproductaccess_755_755  (cost=0.43..221844.23 rows=1866146 width=11) (actual time=0.049..1.037 rows=1043 loops=1)"
"                          Filter: ((location_intspace = 755) AND (location_id = ANY ('{55,60,61,62,64,65,68,69,72,78,79,81,84,85,89,92,97,99,101,102,113,120,121,130,149,187,210,234,597,599,601,602,603,605,606,607,608,610,611,613,632,633,634,635,636,637,865,870,900,910,950,951,952,953,954,955,999,1045,1046,1521,1522,1525,1526,1531,1532}'::integer[])))"
"              ->  Index Scan using products_displayproduct_pkey on products_displayproduct  (cost=0.29..0.31 rows=1 width=712) (actual time=0.001..0.001 rows=1 loops=1043)"
"                    Index Cond: (id = products_displayproductaccess.product_id)"
"Planning Time: 14.706 ms"
"Execution Time: 2.915 ms"

NIKITA's ANSWER (30k stack exchange char limit exceeded) https://pastebin.com/SP5fQq9F

EDIT:

NIKITAS'S ANSWER #2 (updated) Query: https://pastebin.com/raw/hGRVcPJb Explain Analyze: https://pastebin.com/raw/iS1KxDsH

  • General Note: Comments not asking for clarification or suggesting improvements to the question have been removed. Anyone wanting to propose a solution should use the answer box. – Paul White 9 Mar 2 at 9:22
  • now execution plan looks better but I sill see these heap lookups, could you please show testidx2? it looks like it doesn't include location_id. – Nikita Mar 2 at 10:14
  • 1
    Why the restriction "without using a subquery"? If there is a way to solve your issue super fast with a subquery, what is the objection? – ypercubeᵀᴹ Mar 2 at 12:43
  • Please remember to always provide your version of Postgres. Especially essential since partitioning is involved, which has performance issues in Postgres 10 and (to a lesser degree) Postgres 11. And how did you implement partitioning exactly? Doesn't look like declarative partitioning, yet? Also, what's the role of the commented clause -- "products_displayproduct"."placement_id" = 853? – Erwin Brandstetter Mar 3 at 10:57
  • We're using Postgres 11, 12 in the near future. The placement is the sub category the product is placed in. We also have a search_index ts vector we would like to query against along with the other conditions on access, like in stock. DDL Partitioning is something we could do if it made a difference, the tables are generated by Django. Newer releases to support it, we're coming from an older one that build partitions with triggers. – Kevin Parker Mar 9 at 2:27
2
+50

did you try this?:

create index ix_products_displayproductaccess_location_intspace_product_id_etc 
    on products_displayproductaccess (location_intspace, product_id, price desc, is_instock desc) 
    include (location_id);

select product_id, price, is_instock 
from (
       select distinct on (product_id) product_id, price, is_instock 
       from products_displayproductaccess 
       where products_displayproductaccess.location_intspace IN (755) 
              and products_displayproductaccess.location_id IN (55, 60,...) 
       order by product_id, price desc, is_instock desc
      ) as subq 
order by price desc, is_instock desc, product_id limit 100;

select id as product_id, price, is_instock
from products_displayproduct
inner join lateral (select price, is_instock 
      from products_displayproductaccess
      where products_displayproductaccess.product_id = products_displayproduct.id
         and products_displayproductaccess.location_id IN (55, 60,...)  
       order by price desc, is_instock desc
       limit 1)
order by price desc, is_instock desc, product_id limit 100;   

UPDATED
New idea:

CREATE INDEX ix_test ON products_displayproductaccess (location_id, location_intspace, price desc, is_instock desc, product_id);

WITH conditions AS (
    SELECT location_intspace, location_id 
    FROM
        (SELECT UNNEST(STRING_TO_ARRAY('753,754,755,756,757', ',')) AS location_intspace) a
    CROSS JOIN
        (SELECT UNNEST(STRING_TO_ARRAY('1031,1032,1033,1034,1035', ',')) AS location_id) b
)
,products AS (
SELECT DISTINCT ON (product_id) product_id, price, is_instock
FROM conditions
INNER JOIN LATERAL(
                   SELECT price, is_instock, product_id
                   FROM products_displayproductaccess
                   WHERE location_id = conditions.location_id AND location_intspace = conditions.location_intspace 
                   ORDER BY price DESC, is_instock DESC, product_id LIMIT 100
                   ) AS L ON true
ORDER BY product_id, price DESC, is_instock DESC;
)
SELECT product_id, price, is_instock
FROM products
ORDER BY price DESC, is_instock DESC, product_id LIMIT 100;
| improve this answer | |
  • Yes, I tried this on our new Postgresql 12 installation. The performance is extremely poor because of the sub query. Query: paste.depesz.com/s/F3g Explain: explain.depesz.com/s/usyna See above for the lateral, also very very slow. – Kevin Parker Mar 19 at 17:47
  • @KevinParker attached query differs from the original one, now you use more complex condition: "products_displayproductaccess"."location_intspace" IN (753, 754, 755, 756, 757) – Nikita Mar 19 at 18:20
  • It's pretty much the same though, the instpace just lets postgress know which partitions to select from. Also the location_intspace is included in the index anyways. Filtering on a child table only makes very little difference in terms of performance, the problem is the structure of the query needs excessive, just to find distinct with a different order by, that postgres cannot simply walk the indexes of the two tables with lateral or a subquery. – Kevin Parker Mar 19 at 18:32
  • @KevinParker additional sort is added because of this condition (line #4 in explain output), for lateral query it makes things even worse – Nikita Mar 20 at 15:23
  • Yes it does... Find results in two tables with conditions on each, walk indexs, join, one row per left most table, sorted, limit, offset. Without 3 layers of sorts or subqueries I'm just incredibly surprised at how difficult something so seemingly common is on postgres. True experts Laurenz / Erwin, happy pay $500 USD for the right solution (will provide dump). Tempted to throw this up a Maria DB instance and see how this could be done, some missing features like RETURNING were recently implemented, but partitioning support is worse: mariadb.com/kb/en/partitioning-limitations – Kevin Parker Mar 20 at 20:43
1

products_displayproduct doesn't seem to be so big, so I would aim for a hash join with an index only scan on products_displayproductaccess_755_755:

CREATE INDEX ON products_displayproductaccess (location_intspace, location_id)
   INCLUDE (price, is_instock, product_id);

VACUUM products_displayproductaccess_755_755;

For older versions of PostgreSQL that don't support the INCLUDE clause, add the columns to the index keys. For older PostgreSQL versions that don't support partitioned indexes, index the partition.

| improve this answer | |
  • such index is already exists - "pmtest" btree (location_intspace, location_id) INCLUDE (product_id, price, is_instock) – Nikita Mar 2 at 16:40
  • Did you VACUUM the table? – Laurenz Albe Mar 2 at 16:51
  • Yes. Table has been VACUUM ANALYZE FULL on the master table and partitioned natively on PG 12. Tried various queries, the only one that has even close performance for a web app is this one, Query: paste.depesz.com/s/uWT Explain: explain.depesz.com/s/PZKv but the distinct needs to match the order by and it returns duplicates. I'm struggling with how difficult this is in PG12 or is it just me? – Kevin Parker Mar 19 at 18:18
1

The working query you have, should get substantially faster if you do ORDER BY / LIMIT before the join:

SELECT p.id
     , p.date_created
     , p.date_updated
     , p.name
     , p.sub_title
     , p.tags
     , p.has_multiple_variants
     , p.placement_id
     , p.brand_id
     , p.poster_image_id
     , p.rating
     , p.reviews
     , p.is_toppick
     , p.last_amalgamation
     , p.search_index
     , pa.price
     , pa.is_instock
FROM  (
   SELECT *
   FROM  (
      SELECT DISTINCT ON (product_id)
             product_id, price, is_instock
      FROM   products_displayproductaccess
      WHERE  location_intspace = 755
      AND    location_id IN (55, 60, 61, 1532)  -- many more
      ORDER  BY product_id, price DESC, is_instock DESC
      ) sub
   ORDER  BY price DESC, is_instock DESC
   LIMIT  100
   ) pa
JOIN   products_displayproduct p ON p.id = pa.product_id;

Depending on data distribution and typical query predicates, DISTINCT ON in the subquery might be replaced with something more efficient. Maybe a recursive CTE or a LATERAL join to a subquery with ORDER BY / LIMIT. See:

And there is probably more potential to make this faster, yet. Looks like a major project for paid consultant work - starting by sanitizing your table partitioning. The undisclosed triggers after_insert_products_displayproductaccess_trigger and before_insert_products_displayproductaccess_trigger look like a sub-optimal implementation.

| improve this answer | |
  • Just realized that Nikita already suggests a very similar query. Leaving the post for the additional details. – Erwin Brandstetter Mar 3 at 11:45
  • So the problem with this query is it doesn't leave room for any conditions on products_displayproduct... We have a placement_id for product placement and a tsvector column for text searches. – Kevin Parker Mar 5 at 9:12
  • I asked about the commented clause -- "products_displayproduct"."placement_id" = 853 above, but no response. And search_index wasn't mentioned as part of the WHERE clause. Both make a difference ... – Erwin Brandstetter Mar 6 at 16:51
  • Hi Erwin, I do apologize for not getting a response to you sooner about that condition before the query was optimized. I've been super busy... It is an important condition as is being able to use the search_index field with ts_queries. – Kevin Parker Mar 8 at 21:18
  • This isn't exact, if we add condition to the join like placement_id, the results are incorrect. – Kevin Parker Mar 12 at 19:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.