Postgres. the query sometimes to long.

I need help or any hint. I have Postgres DB 9.4 and have one query processed very slow SOMETIMES.

SELECT COUNT(*) FROM "table_a" INNER JOIN "table_b" ON "table_b"."id" = "table_a"."table_b_id" AND "table_b"."deleted_at" IS NULL WHERE "table_a"."deleted_at" IS NULL AND "table_b"."company_id" = ? AND "table_a"."company_id" = ?

Query plan for this -

Aggregate (cost=308160.70..308160.71 rows=1 width=0)
             -> Hash Join (cost=284954.16..308160.65 rows=20 width=0)
                                    Hash Cond: ?
    -> Bitmap Heap Scan on table_a (cost=276092.39..299260.96 rows=6035 width=4)
                                    Recheck Cond: ?
                                                Filter: ?
-> Bitmap Index Scan on index_table_a_on_created_at_and_company_id (cost=0.00..276090.89 rows=6751 width=0)
                                         Index Cond: ?
            -> Hash (cost=8821.52..8821.52 rows=3220 width=4)
        -> Bitmap Heap Scan on table_b (cost=106.04..8821.52 rows=3220 width=4)
                                Recheck Cond: ?
                                                Filter: ?
    -> Bitmap Index Scan on index_ table_b_on_company_id (cost=0.00..105.23 rows=3308 width=0)
                                        Index Cond: ? 

But usually, this is query executed enough fast (about 69.7ms). I don't understand why this happened sometimes. I saw in performance logs by this period, that my RDS instance consumes a lot of memory and count this queries reaches about 100 per seconds. so guys, any helps please, where do I move for solve this problem.

Answers


I am not sure if this will solve your problem or not :)

  • When this query is returning very fast result it is returning result from cache and not executing query again and not preparing result at that time.

  • First of all you have to check if there are too much queries are being executed on these tables, especially inserts/updated/deletes. This type of queries are causing locking and select have to wait until lock is being released.

  • Query can be slow because there is too much comparison cost of join and where clause between table_a and table_b.

  • You can reduce your cost by applying indexes to columns "table_b"."id", "table_a"."table_b_id", "table_a"."deleted_at", "table_b"."company_id", AND "table_a"."company_id".

  • You can create a view to reduce the cost as well. Views are returning cached information.

  • One last thing is you can reduce cost by using temporary table as well. I have given an example below.

QUERIES:

CREATE TEMPORARY TABLE table_a_temp as 
SELECT "table_a"."table_b_id" FROM "table_a" 
WHERE "table_a"."deleted_at" IS NULL AND "table_a"."company_id" = ? ;

CREATE TEMPORARY TABLE table_b_temp as 
SELECT "table_b"."id" FROM "table_a" 
WHERE"table_b"."deleted_at" IS NULL AND "table_b"."company_id" = ?;

SELECT COUNT(*) FROM "table_a_temp" INNER JOIN "table_b_temp" 
ON "table_b_temp"."id" = "table_a_temp"."table_b_id" ;

Need Your Help

Call a PHP function after AJAX

javascript php jquery ajax

When a user submits the form on my page I use AJAX to submit the information without refreshing the page. After the user submits the information I want to run a PHP function that I have already wri...

Find the upper left coordinate of a panel when scrolling

.net winforms scroll

How to find the upper left coordinate of a panel when scrolling? (.net 2)