PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Slow Execution Time

I have a request to add new column that will count distinct column named kode_buatan from january to the month in the record (so if the record have date in march 2024 count distinct kode buatan from january 2024 to march 2024) so i made this query

WITH coba1 AS (
  SELECT * FROM table_A
)
SELECT coba1.*, 
  (SELECT COUNT(DISTINCT kode_buatan) 
   FROM table_A sd 
   WHERE EXTRACT(MONTH FROM sd.date) <= EXTRACT(MONTH FROM coba1.date) 
     AND EXTRACT(YEAR FROM sd.date) <= EXTRACT(YEAR FROM coba1.date) 
     AND sd.shipto = coba1.shipto) AS calculated_column_name
FROM coba1;

then there is problem that the data is duplicate so i add distinct and it turn the execution time so long (unfinished). can someone give me some advice to increasing performance of the query? thank you

I have tried

WITH coba1 AS (
  SELECT * FROM table_A
)
SELECT coba1.*, 
  (SELECT COUNT(DISTINCT kode_buatan) 
   FROM table_A sd 
   WHERE EXTRACT(MONTH FROM sd.date) <= EXTRACT(MONTH FROM coba1.date) 
     AND EXTRACT(YEAR FROM sd.date) <= EXTRACT(YEAR FROM coba1.date) 
     AND sd.shipto = coba1.shipto) AS calculated_column_name
FROM

and

create table coba2 as (
select extract(year from date) as TAHUN, extract(month from date) as BULAN, shipto, (select count(distinct kode_buatan) from salesout_doi sd where extract(month from sd.date) <= extract(month from coba1.date) and extract(year from sd.date) = extract(year from coba1.date) and sd.shipto = coba1.shipto ) from salesout_doi_cobaaaytd coba1 where extract(year from date) = 2024
)

select distinct * from coba2
Tags:

3

Answers


  1. 0

    First check whether there is any Index for Column kode_buatan.
    If there is no index, you should create a new index for that column.

    Also recommended to Use Display Execution Plan to do a better optimization of your query.

    Login or Signup to reply.
  2. 0

    You don’t need all that stuff and I think your query is false…

    Try :

    SELECT coba1.*, COUNT(DISTINCT kode_buatan) AS calculated_column_name
FROM   table_A AS sd JOIN table_A AS coba1
       ON sd.shipto = coba1.shipto
       AND (EXTRACT(YEAR FROM sd.date) <= EXTRACT(YEAR FROM coba1.date) 
            OR (EXTRACT(YEAR FROM sd.date) <= EXTRACT(YEAR FROM coba1.date)
                AND EXTRACT(MONTH FROM sd.date) <= EXTRACT(MONTH FROM coba1.date) )
GROUP  BY coba1.*

    Replace "coba1.*" by the exact enumeration of the column’s name…

    A way to have more performance would by to add a computed column in the table with the concatenation of year an mont, in the form : YYYYMM

    Login or Signup to reply.
  3. 0

    Your where clause looks wrong to me… it will match all previous January to March periods regardless of year. Try changing your query to:

    SELECT o.*, (
    SELECT COUNT(DISTINCT kode_buatan)
    FROM table_a AS x
    WHERE x.shipto = o.shipto
    AND   x.date >= date_trunc('year', o.date)
    AND   x.date <  date_trunc('month', o.date) + interval '1 month'
) AS calculated_column_name
FROM table_a AS o
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search