Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a below query which will hit database every one minute


SQL
SELECT n.notificationid ,n.notificationtype 
FROM notification n
INNER JOIN usernotification un ON n.notificationid = un.notificationid
LEFT JOIN vhctotal vt ON vt.vhcid = n.vhcid
WHERE CASE WHEN n.notificationtype = 2 THEN n.datechecked = '' ELSE to_timestamp(n.datechecked, 'DD/MM/YYYY hh24:mi:ss') > NOW() - INTERVAL '30 days' END
    AND date_trunc('day', n.vhcdate::TIMESTAMP) = CURRENT_DATE
    AND un.commentstatus = 0
    AND un.userid = 30427
    OR (
        un.commentstatus = 1
        AND un.updateddatetime > current_timestamp - interval '5 minutes'
        )



The above query taking a bit long time to execute. when I check those tables Indexing not done properly.

I would like to create indices for those tables. Should I have to create a index for the columns which are used in WHERE clause ? Does it give better performance ? Please suggest .

What I have tried:

QUERY PLAN
SQL
Nested Loop Left Join  (cost=25.96..2136.39 rows=1 width=237) (actual time=49.911..49.911 rows=0 loops=1)                                                                                                                                                                                                                                                                                                                                                                  
  Join Filter: (vt.vhcid = n.vhcid)                                                                                                                                                                                                                                                                                                                                                                                                                                        
  ->  Hash Join  (cost=25.96..2089.81 rows=1 width=163) (actual time=49.908..49.908 rows=0 loops=1)                                                                                                                                                                                                                                                                                                                                                                        
        Hash Cond: (un.notificationid = n.notificationid)                                                                                                                                                                                                                                                                                                                                                                                                                  
        Join Filter: ((CASE WHEN ((n.notificationtype)::text = '2'::text) THEN ((n.datechecked)::text = ''::text) ELSE (to_timestamp((n.datechecked)::text, 'DD/MM/YYYY hh24:mi:ss'::text) > (now() - '30 days'::interval)) END AND (date_trunc('day'::text, (n.vhcdate)::timestamp without time zone) = ('now'::cstring)::date) AND (un.commentstatus = 0) AND (un.userid = 30427)) OR ((un.commentstatus = 1) AND (un.updateddatetime > (now() - '00:05:00'::interval))))
        Rows Removed by Join Filter: 26406                                                                                                                                                                                                                                                                                                                                                                                                                                 
        ->  Seq Scan on usernotification un  (cost=0.00..528.36 rows=26136 width=36) (actual time=0.009..3.937 rows=26406 loops=1)                                                                                                                                                                                                                                                                                                                                         
        ->  Hash  (cost=18.76..18.76 rows=576 width=127) (actual time=0.420..0.420 rows=577 loops=1)                                                                                                                                                                                                                                                                                                                                                                       
              Buckets: 1024  Batches: 1  Memory Usage: 96kB                                                                                                                                                                                                                                                                                                                                                                                                                
              ->  Seq Scan on notification n  (cost=0.00..18.76 rows=576 width=127) (actual time=0.004..0.105 rows=577 loops=1)                                                                                                                                                                                                                                                                                                                                            
  ->  Seq Scan on vhctotal vt  (cost=0.00..33.48 rows=1048 width=74) (never executed)                                                                                                                                                                                                                                                                                                                                                                                      
Total runtime: 50.033 ms   
Posted
Updated 8-Mar-17 19:24pm
v3
Comments
Manoj Kumar Choubey 9-Mar-17 2:31am    
I can not give you perfect answer without your database structure, but first thing you need to do check your database is normalized and create primary keys and or foreign keys as needed for example "notificationid" may be a primary key of your table.
second thing you are trying some query plan try this in query profiler and query analyzer.
Richard Deeming 9-Mar-17 8:10am    
to_timestamp(n.datechecked, 'DD/MM/YYYY hh24:mi:ss')

Are you storing date/time values in a string column? If so, that's an obvious thing to fix.

date_trunc('day', n.vhcdate::TIMESTAMP) = CURRENT_DATE

You should generally avoid performing calculations on table columns in the WHERE clause, as it will prevent your query from using an index on that column. Instead of truncating the time and comparing the result to a date, test whether the column is greater than or equal to the date, and less than one day after the date:
AND n.vhcdate >= CURRENT_DATE AND n.vhcdate < CURRENT_DATE + INTERVAL '1 day'
King Fisher 10-Mar-17 2:59am    
that's y its was written like this,
King Fisher 10-Mar-17 2:58am    
thank you , but this column was created as varchar , I cant able to use this condition AND n.vhcdate >= CURRENT_DATE AND n.vhcdate < CURRENT_DATE + INTERVAL '1 day', It shows "operator does not exist: character varying = date"
Richard Deeming 10-Mar-17 7:32am    
Well, there's the obvious place to start! :)

You should never store dates as strings. Always use one of the available date/time types instead.

Not only will it make your queries faster, it will prevent you from storing invalid or ambiguous data in the column.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900