CODEWITHSUNDEEP
postgresqlpostgresql-12pgpool
Neeraj Mehta
Neeraj Mehta

Reputation: 1705

Postgres Simple Query is taking time around 2 Time

I've noticed that my query execution time increases significantly when I add filters such as visit_date or sort by ib.id. It takes around 2 minutes to execute. Here is the code:

SELECT *
FROM (
SELECT ib.id,
    ben.uid,
    ib.pid,
    ben.first_name,
    ben.middle_name,
    ben.last_name,
    iv.visit_date, 
    ben.id AS beneficiary_id,
    ben.mobile_number,
    ben.age,
    iv.is_pregnant,
    itr.tested_date,
    ben.hiv_status_id AS hiv_status,
    ben.hiv_type_id AS hiv_type,
    ben.date_of_birth,
    itr.result_status,
    ib.recent_visit_id AS visit_id,
    ib.beneficiary_status,
    ben.gender_id,
    ib.is_active AS ictc_ben_is_active,
    ib.is_deleted AS ictc_ben_is_deleted,
    ib.deleted_reason AS ictc_ben_deleted_reason,
    ib.deleted_reason_comment AS ictc_ben_deleted_reason_comment,
    ib.facility_id AS registered_facility_id,
    ibs.name AS beneficiary_status_desc,
    hs.name AS hiv_status_desc, 
    ib.infant_code as infant_code  
 FROM soch.ictc_beneficiary ib 
  JOIN soch.beneficiary ben ON ib.beneficiary_id = ben.id 
  LEFT JOIN soch.ictc_test_result itr ON ib.current_test_result_id = itr.id
  LEFT JOIN soch.ictc_visit iv ON ib.recent_visit_id = iv.id
  LEFT JOIN soch.master_hiv_status hs ON ben.hiv_status_id = hs.id
  LEFT JOIN soch.master_ictc_beneficiary_status ibs ON ib.beneficiary_status = ibs.id
where ib.facility_id = 13649 and ben.category_id <>1 
and  ben.is_active = true --and (ben.is_delete = false or ben.is_delete = null)
and (ben.benf_search_str like '%%' or ib.pid like '%%') 
) AS ordered_data where ordered_data.visit_date >= '2024-04-15'
limit 10;

Here is Explain Analyse of the Query:

    Node Type   Entity  Cost    Rows    Time    Condition
Limit   [NULL]  2.26 - 2784.90  10  57874.052   [NULL]
Nested Loop [NULL]  2.26 - 33393.90 10  57874.037   [NULL]
Nested Loop [NULL]  2.26 - 33287.67 10  57873.995   [NULL]
Nested Loop [NULL]  2.26 - 33278.41 10  57873.941   [NULL]
Nested Loop [NULL]  1.69 - 32947.84 10  57686.039   [NULL]
Nested Loop [NULL]  1.13 - 32613.39 10  57599.678   [NULL]
Index Scan  ictc_beneficiary    0.56 - 9424.30  7068    14378.517   (facility_id = 13649)
Index Scan  ictc_visit  0.56 - 2.76 0   6.114   (id = ib.recent_visit_id)
Index Scan  beneficiary 0.56 - 2.78 1   8.631   (id = ib.beneficiary_id)
Index Scan  ictc_test_result    0.56 - 2.75 1   18.786  (id = ib.current_test_result_id)
Materialize [NULL]  0.00 - 1.07 1   0.003   [NULL]
Seq Scan    master_hiv_status   0.00 - 1.05 1   0.014   [NULL]
Materialize [NULL]  0.00 - 1.88 3   0.002   [NULL]
Seq Scan    master_ictc_beneficiary_status  0.00 - 1.59 3   0.007   [NULL]

I have an index at facility_id as well as at visit_data. Any idea, I am struggling with this since many days

Adding explain(analyze, verbose, buffers, settings) of the Query. enter image description here

Upvotes: 0

Views: 57

Answers (1)

Luuk
Luuk

Reputation: 14891

DDL, and indexes are important when query speed needs to be improved.

Doing the improvement is a step-by-step process, which needs to be done with care. By doing this in steps you will learn about how to improve the speed of your queries in general, which is of help with the next SQL statement that needs improving.

Some small steps, to get you started:

For example the table soch.beneficiary

Is there in index that can be used with expression in the WHERE-clause that says:

ben.is_active = true

When no such index exists all records of soch.beneficiary needs to be joined to the result, and can only be filtered after doing that (Because only then the result value, for the field is_active, will be known). The index should contain both the is_active and the id field!)

Another tip, to get better readability is to remove the expression from the WHERE-clause and add it to the ON-clause, so for the table soch.beneficiary it would lead to:

SELECT
    ...
FROM soch.ictc_beneficiary ib 
JOIN soch.beneficiary ben ON ib.beneficiary_id = ben.id AND ben.is_active = true

Next one would be: ben.benf_search_str like '%%'

I think ben.benf_search_str not is null will be faster? (Again, and index on benf_search_str might improve this)

When looking at iv.visit_date, which has in the final WHERE-clause visit_date >= '2024-04-15' should be improbed. Because of this WHERE-clause the LEFT JOIN to soch.ictc_visit is effectively an INNER JOIN, where you can add this filter on visit_date, which can be improved by a proper INDEX.

Finally on the output (of apparently DBeaver): When typing the query:

EXPLAIN (analyze,verbose, buffers, settings) SELECT .....

You should not copy this as a picture, But on the results view you should see 'Grid' and 'Text', when choosing 'Text' you can copy/paste the text, which is more readable than a picture.

Upvotes: 1

Related Questions