1

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?

  • 2
    Did you try EXPLAIN and EXPLAIN ANALYZE? Is rscode really a string? You will get better performances with integers... No index on latitude? Also the DISTINCT is probably unnecessary since you use IN. – Patrick Mevzek Jul 24 '18 at 15:37
  • @PatrickMevzek Unfortunately rscode has to be a string since some of the values have - in them. – Devdatta Tengshe Jul 24 '18 at 15:56
  • An index on (rscode, hul_code) might help. – mustaccio Jul 24 '18 at 16:20
  • 1
    More information on data distribution would help. How many distinct values in rscode? How many rows in table store? Is referential integrity between sale_date.hul_code and store.hul_code enforced (with a FK constraint)? How many distinct hul_code in sale_data? – Erwin Brandstetter Jul 24 '18 at 16:47
2

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:

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:

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.

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.