PG SQL query return timeout when search

Hi, I have problem with my pgsql query:

When i have this query:

SELECT COUNT(*) AS count
		FROM address zk
		INNER JOIN dic_data t ON zk.fk_typpm = t.id
		INNER JOIN dic_data k ON zk.fk_kierunekpm = k.id
		LEFT JOIN dic_data r ON zk.fk_rolapm = r.id
		WHERE zk.pm = zk.id
		AND UPPER(t.code) = UPPER('EMAIL')
		AND k.code = 'CUSTOMER'
		AND zk.contakt BETWEEN '2024-04-01 00:00:00' AND '2024-04-30 23:59:59'
	 AND r.code IN ('XX', 'YYY')

I have very long loading and timeout. Then i remove from my code: AND k.code = 'CUSTOMER' - then it's working.

What's wrong? How can i repair it? Please help me.

Tray2

2 years ago

Level 73

Prefix the query with EXPLAIN and share the result

https://www.postgresql.org/docs/current/using-explain.html

trifek

2 years ago

Level 3

@tray2 in result i have:

Aggregate  (cost=90107.27..90107.28 rows=1 width=8) (actual time=11722.747..11722.749 rows=1 loops=1)
  ->  Nested Loop  (cost=1.13..90107.26 rows=1 width=0) (actual time=4.453..11716.932 rows=39419 loops=1)
        ->  Nested Loop  (cost=0.85..90106.52 rows=1 width=4) (actual time=4.444..11551.166 rows=140379 loops=1)
              ->  Nested Loop  (cost=0.56..90104.28 rows=3 width=8) (actual time=4.425..11297.367 rows=144987 loops=1)
                    Join Filter: (zk.fk_typpm = t.id)
                    Rows Removed by Join Filter: 1719477
                    ->  Index Scan using address_contakt_idx on address zk  (cost=0.56..89471.39 rows=685 width=12) (actual time=1.581..10996.194 rows=310744 loops=1)
                          Index Cond: ((contakt >= '2024-04-01 00:00:00'::timestamp without time zone) AND (contakt <= '2024-04-30 23:59:59'::timestamp without time zone))
                          Filter: (pm = id)
                          Rows Removed by Filter: 57572
                    ->  Materialize  (cost=0.00..232.27 rows=39 width=4) (actual time=0.000..0.000 rows=6 loops=310744)
                          ->  Seq Scan on dic_data t  (cost=0.00..232.07 rows=39 width=4) (actual time=0.402..2.828 rows=6 loops=1)
                                Filter: (upper((code)::text) = 'EMAIL'::text)
                                Rows Removed by Filter: 7700
              ->  Index Scan using idx_dic_data_id on dic_data k  (cost=0.28..0.73 rows=1 width=4) (actual time=0.001..0.001 rows=1 loops=144987)
                    Index Cond: (id = zk.fk_kierunekpm)
                    Filter: ((code)::text = 'CUSTOMER'::text)
                    Rows Removed by Filter: 0
        ->  Index Scan using idx_dic_data_id on dic_data r  (cost=0.28..0.73 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=140379)
              Index Cond: (id = zk.fk_rolapm)
              Filter: ((code)::text = ANY ('{KLIEN,SERW}'::text[]))
              Rows Removed by Filter: 0
Planning Time: 11.565 ms
Execution Time: 11723.651 ms

Tray2

2 years ago

Level 73

@trifek You need to look at the parts with the high cost. since they are the things slowing the query down.

Please or to participate in this conversation.