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' )
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
to_timestamp(n.datechecked, 'DD/MM/YYYY hh24:mi:ss')
date_trunc('day', n.vhcdate::TIMESTAMP) = CURRENT_DATE
WHERE
AND n.vhcdate >= CURRENT_DATE AND n.vhcdate < CURRENT_DATE + INTERVAL '1 day'
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)