How to improve performance of query for distinct suppliers in big sales table?

Question

I have two tables, one with store data:

              Table "public.store"
  Column   |          Type          | Modifiers
-----------+------------------------+-----------
 id        | integer                | not null
 hul_code  | character varying      |
 latitude  | numeric                |
 longitude | numeric                |
 name      | character varying(100) |
Indexes:
    "store_pkey" PRIMARY KEY, btree (id)
    "store-hlcode-idx" btree (hul_code)

Another with the sales data:

         Table "public.sale_data"
  Column   |       Type        | Modifiers
-----------+-------------------+-----------
 id        | integer           | not null
 hul_code  | character varying |
 partyname | character varying |
 rscode    | character varying |
 rsname    | character varying |
 cluster   | character varying |
 channel   | character varying |
 basepack  | character varying |
 total_16  | numeric           |
 total_17  | numeric           |
 total_18  | numeric           |
Indexes:
    "sale_data_pkey" PRIMARY KEY, btree (id)
    "hul-code-idx" btree (hul_code)
    "sd-bp-idx" btree (basepack)
    "sd-rscd-idx" btree (rscode)

The second table has over 11 million rows.

I'm trying to get the list of all stores which had items coming from a particular Regional Supplier (identified by rscode) by using a query like:

Select s.hul_code, s.name, s.latitude, s.longitude
from store s 
Where hul_code in 
  (Select Distinct(hul_code) as hulcode
   from sale_data
   where rscode='133955')
AND s.latitude is not null;

This query takes more than 1.5 to 2 seconds on average.

How to increase the speed of this query?

Answer 1

For typical data distributions, emulating a loose index scan should be the magic trick for you.

Requires a multicolumn index to be fast:

CREATE INDEX ON sale_data(rscode, hul_code);

The variant with correlated subquery in the rCTE should be fastest for the simple case:

WITH RECURSIVE cte AS (
   SELECT min(hul_code) AS hul_code
   FROM   sale_data
   WHERE  rscode = '133955'    -- provide rscode here

   UNION ALL
   SELECT (SELECT hul_code
           FROM   sale_data
           WHERE  rscode = '133955'     -- repeat rscode here
           AND    hul_code > c.hul_code
           ORDER  BY 1
           LIMIT  1
           )
   FROM   cte c
   )
SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM   cte c
JOIN   store s USING (hul_code);

Detailed explanation:

• Optimize GROUP BY query to retrieve latest record per user

There may be faster alternatives for particular data distributions.

Asides:

DISTINCT might make sense for data distributions with very few rows per distinct (rscode, hul_code) (producing large numbers of resulting rows). But what you have doesn't make a lot of sense:

IN (Select Distinct(hul_code) as hulcode ... )

The column alias (as hulcode) is noise since IN ignores column names.

DISTINCT does not require parentheses. (hul_code) is interpreted as ROW constructor, short for ROW(hul_code). The row wrapper happens to be stripped automatically for the single column in this case. But you might introduce complications for cases with multiple columns.

There is the Postgres variant DISTINCT ON () which requires parentheses, but that's not used (nor needed) here. Related:

• Select first row in each GROUP BY group?

But none of this makes a lot of sense with an IN construct, where you might just drop DISTINCT altogether. Better, yet, rewrite as:

SELECT s.hul_code, s.name, s.latitude, s.longitude
FROM  (
   SELECT DISTINCT hul_code
   FROM   sale_data
   WHERE  rscode='133955'
   ) sd
JOIN  store s USING (hul_code);

.. in the unlikely case that you should have such a data distribution.

And your columns rscode seems to hold numeric data. If so, it should be a numeric type, not varchar.