Query running for 12 hours

Hi everybody,

*Today I saw that two queries have been running for more then 12 hours(They
are the same query actually).*

*mydb=# select * from pg_stat_activity where state='active' and
state_change < now()-'12 hours'::interval;*

-[ RECORD 1
]----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
datid | 49426
datname | mydb
pid | 7127
usesysid | 19467
usename | myuser
application_name |
client_addr | myip
client_hostname |
client_port | 49728
backend_start | 2018-05-30 02:00:00.011561+03
xact_start | 2018-05-30 02:00:00.02989+03
query_start | 2018-05-30 02:00:00.121761+03
state_change | 2018-05-30 02:00:00.121772+03
waiting | f
state | active
backend_xid |
backend_xmin | 89519517
query | select this_.ID as ID107_1_, this_.islemTuru as
islemTuru107_1_, this_.kullanici_ID as kullanici7_107_1_,
this_.loglananIslem as loglanan3_107_1_, this_.oncekiVeri as
oncekiVeri107_1_, this_.tarih as tarih107_1_, this_.veri as veri107_1_,
kullanici2_.ID as ID103_0_, kullanici2_.ad as ad103_0_, kullanici2_.aktif
as aktif103_0_, kullanici2_.dbUser as dbUser103_0_, kullanici2_.ePosta as
ePosta103_0_, kullanici2_.kullaniciAdi as kullanic6_103_0_,
kullanici2_.kullaniciTipiEnum as kullanic7_103_0_, kullanici2_.parola as
parola103_0_, kullanici2_.soyad as soyad103_0_ from t_log_kaydi this_ left
outer join t_kullanici kullanici2_ on this_.kullanici_ID=kullanici2_.ID
where this_.tarih<$1
-[ RECORD 2
]----+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
datid | 49426
datname | mydb
pid | 7129
usesysid | 19467
usename | myuser
application_name |
client_addr | myip
client_hostname |
client_port | 49730
backend_start | 2018-05-30 02:00:00.012887+03
xact_start | 2018-05-30 02:00:00.03225+03
query_start | 2018-05-30 02:00:00.114039+03
state_change | 2018-05-30 02:00:00.114048+03
waiting | f
state | active
backend_xid |
backend_xmin | 89519517
query | select this_.ID as ID4_1_, this_.islemTuru as
islemTuru4_1_, this_.kullanici_ID as kullanici7_4_1_, this_.loglananIslem
as loglanan3_4_1_, this_.oncekiVeri as oncekiVeri4_1_, this_.tarih as
tarih4_1_, this_.veri as veri4_1_, kullanici2_.ID as ID0_0_, kullanici2_.ad
as ad0_0_, kullanici2_.aktif as aktif0_0_, kullanici2_.dbUser as
dbUser0_0_, kullanici2_.ePosta as ePosta0_0_, kullanici2_.kullaniciAdi as
kullanic6_0_0_, kullanici2_.kullaniciTipiEnum as kullanic7_0_0_,
kullanici2_.parola as parola0_0_, kullanici2_.soyad as soyad0_0_ from
t_log_kaydi this_ left outer join t_kullanici kullanici2_ on
this_.kullanici_ID=kullanici2_.ID where this_.tarih<$1

*I've explained this query(I used $1 as now())*

*mydb=# explain select this_.ID as ID107_1_, this_.islemTuru as
islemTuru107_1_, this_.kullanici_ID as kullanici7_107_1_,
this_.loglananIslem as loglanan3_107_1_, this_.oncekiVeri as
oncekiVeri107_1_, this_.tarih as tarih107_1_, this_.veri as veri107_1_,
kullanici2_.ID as ID103_0_, kullanici2_.ad as ad103_0_, kullanici2_.aktif
as aktif103_0_, kullanici2_.dbUser as dbUser103_0_, kullanici2_.ePosta as
ePosta103_0_, kullanici2_.kullaniciAdi as kullanic6_103_0_,
kullanici2_.kullaniciTipiEnum as kullanic7_103_0_, kullanici2_.parola as
parola103_0_, kullanici2_.soyad as soyad103_0_ from t_log_kaydi this_ left
outer join t_kullanici kullanici2_ on this_.kullanici_ID=kullanici2_.ID
where this_.tarih<now();*
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Left Join (cost=388.90..62945.67 rows=1166999 width=503)
Hash Cond: (this_.kullanici_id = kullanici2_.id)
-> Seq Scan on t_log_kaydi this_ (cost=0.00..46529.99 rows=1166999
width=374)
Filter: (tarih < now())
-> Hash (cost=278.40..278.40 rows=8840 width=129)
-> Seq Scan on t_kullanici kullanici2_ (cost=0.00..278.40
rows=8840 width=129)
(6 rows)

*So I explain analyzed this query, it took about 6 seconds.*
*mydb=# explain(analyze,verbose,buffers) select this_.ID as ID107_1_,
this_.islemTuru as islemTuru107_1_, this_.kullanici_ID as
kullanici7_107_1_, this_.loglananIslem as loglanan3_107_1_,
this_.oncekiVeri as oncekiVeri107_1_, this_.tarih as tarih107_1_,
this_.veri as veri107_1_, kullanici2_.ID as ID103_0_, kullanici2_.ad as
ad103_0_, kullanici2_.aktif as aktif103_0_, kullanici2_.dbUser as
dbUser103_0_, kullanici2_.ePosta as ePosta103_0_, kullanici2_.kullaniciAdi
as kullanic6_103_0_, kullanici2_.kullaniciTipiEnum as kullanic7_103_0_,
kullanici2_.parola as parola103_0_, kullanici2_.soyad as soyad103_0_ from
t_log_kaydi this_ left outer join t_kullanici kullanici2_ on
this_.kullanici_ID=kullanici2_.ID where this_.tarih<now();*

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------
Hash Left Join (cost=388.90..62945.67 rows=1166999 width=503) (actual
time=73.423..4781.951 rows=1170716 loops=1)
Output: this_.id, this_.islemturu, this_.kullanici_id,
this_.loglananislem, this_.oncekiveri, this_.tarih, this_.veri,
kullanici2_.id, kullanici2_.ad, kullanici2_.aktif, kullanici2_.dbuser, kull
anici2_.eposta, kullanici2_.kullaniciadi, kullanici2_.kullanicitipienum,
kullanici2_.parola, kullanici2_.soyad
Hash Cond: (this_.kullanici_id = kullanici2_.id)
Buffers: shared hit=29215
-> Seq Scan on public.t_log_kaydi this_ (cost=0.00..46529.99
rows=1166999 width=374) (actual time=0.013..1783.133 rows=1170716 loops=1)
Output: this_.id, this_.islemturu, this_.loglananislem,
this_.oncekiveri, this_.tarih, this_.veri, this_.kullanici_id
Filter: (this_.tarih < now())
Buffers: shared hit=29025
-> Hash (cost=278.40..278.40 rows=8840 width=129) (actual
time=73.284..73.284 rows=9134 loops=1)
Output: kullanici2_.id, kullanici2_.ad, kullanici2_.aktif,
kullanici2_.dbuser, kullanici2_.eposta, kullanici2_.kullaniciadi,
kullanici2_.kullanicitipienum, kullanici2_.parola, kullanici2_.
soyad
Buckets: 16384 Batches: 1 Memory Usage: 1604kB
Buffers: shared hit=190
-> Seq Scan on public.t_kullanici kullanici2_ (cost=0.00..278.40
rows=8840 width=129) (actual time=0.008..34.170 rows=9134 loops=1)
Output: kullanici2_.id, kullanici2_.ad, kullanici2_.aktif,
kullanici2_.dbuser, kullanici2_.eposta, kullanici2_.kullaniciadi,
kullanici2_.kullanicitipienum, kullanici2_.parola, kullan
ici2_.soyad
Buffers: shared hit=190
Planning time: 0.645 ms
Execution time: 5938.714 ms
(17 rows)

*PostgreSQL version is 9.5.3 on CentOS 7.2.1511*

*[root(at)myserver]# cat /etc/redhat-release*
CentOS Linux release 7.2.1511 (Core)

*mydb=# select version();*
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.5.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)

I could not figure out what the problem is.
I would be happy if someone could help me to solve this situation.

--

Saygılarımla

*Yavuz Selim Sertoğlu*

Veritabanı Uzmanı

T 0 312 220 1 220

F 0 312 286 00 10
M 0 542 728 08 02

*yavuzselim(dot)sertoglu(at)bisoft(dot)com(dot)tr <yavuzselim(dot)sertoglu(at)bisoft(dot)com(dot)tr>*