so 17. 4. 2021 v 17:09 odesÃlatel Justin Pryzby <pry...@telsasoft.com> napsal:
> On Sat, Apr 17, 2021 at 04:36:52PM +0200, Pavel Stehule wrote: > > today I worked on postgres's server used for critical service. Because > the > > application is very specific, we had to do final tuning on production > > server. I fix lot of queries, but I am not able to detect fast queries > that > > does full scan of middle size tables - to 1M rows. Surely I wouldn't log > > all queries. Now, there are these queries with freq 10 per sec. > > > > Can be nice to have a possibility to set a log of queries that do full > > scan and read more tuples than is specified limit or that does full scan > of > > specified tables. > > > > What do you think about the proposed feature? > > Are you able to use auto_explain with auto_explain.log_min_duration ? > Unfortunately, I cannot use it. This server executes 5K queries per seconds, and I am afraid to decrease log_min_duration. The logs are forwarded to the network and last time, when users played with it, then they had problems with the network. I am in a situation where I know there are queries faster than 100ms, I see so there should be fullscans from pg_stat_user_tables, but I don't see the queries. The fullscan of this table needs about 30ms and has 200K rows. So decreasing log_min_duration to this value is very risky. > Then you can search for query logs with > message ~ 'Seq Scan .* \(actual time=[.0-9]* rows=[0-9]{6,} loops=[0-9]*)' > > Or can you use pg_stat_all_tables.seq_scan ? > I use pg_stat_all_tables.seq_scan and I see seq scans there. But I need to know the related queries. > But it seems to me that filtering on the duration would be both a more > important criteria and a more general one, than "seq scan with number of > rows". > > | (split_part(message, ' ', 2)::float/1000 AS duration ..) WHERE > duration>2222; > > -- > Justin >