Обсуждение: BUG #15008: Need a pause
The following bug has been logged on the website: Bug reference: 15008 Logged by: Laurent Martelli Email address: martellilaurent@gmail.com PostgreSQL version: 9.6.6 Operating system: Debian 9.3 Description: I have a set of scripts I execute with psql, and for some reason, a query in one of them take forever (never had the patience to wait for more than 15 minutes), eating 1 CPU, unless I insert a pause (sleep 60) before that script, and then it only takes a few seconds. It only occurs when the tables we operate on have a sufficient number of rows. When we do "unit" tests with a subset of the data, it all goes smoothly. Please, tell me what additional information I can provide to help fix this.
> The following bug has been logged on the website: > > Bug reference: 15008 > Logged by: Laurent Martelli > Email address: martellilaurent@gmail.com > PostgreSQL version: 9.6.6 > Operating system: Debian 9.3 > Description: > > I have a set of scripts I execute with psql, and for some reason, a query in one of them take forever (never had the patienceto wait for more than 15 minutes), eating 1 CPU, unless I insert a pause (sleep 60) before that script, and thenit only takes a few seconds. > > It only occurs when the tables we operate on have a sufficient number of rows. When we do "unit" tests with a subset ofthe data, it all goes smoothly. > > Please, tell me what additional information I can provide to help fix this. It sounds like you're bulk-loading rows, and not updating the table statistics before running a query. The pause may giveautovacuum time to find and analyze the table so you get the correct query plan. I'd suggest you modify your scriptto add EXPLAIN ANALYZE to your query and then run it with and without the pause. Probably you'll find one is doinga full table scan, and the other is using an index. More than likely you will need to add an "ANALYZE table_name;" into your script in place of the pause Greg Clough Senior Technology Engineer O | +44 (0) 20 7665 9787 M | +44 (0) 7803 142 292 Ipreo Castle House | 37-35 Paul St | London EC2A 4LS
You are absolutely right !
Sorry for the noise here. Next time, I'll post on the user ML first.2018-01-12 10:39 GMT+01:00 Greg Clough <greg.clough@ipreo.com>:
> The following bug has been logged on the website:
>
> Bug reference: 15008
> Logged by: Laurent Martelli
> Email address: martellilaurent@gmail.com
> PostgreSQL version: 9.6.6
> Operating system: Debian 9.3
> Description:
>
> I have a set of scripts I execute with psql, and for some reason, a query in one of them take forever (never had the patience to wait for more than 15 minutes), eating 1 CPU, unless I insert a pause (sleep 60) before that script, and then it only takes a few seconds.
>
> It only occurs when the tables we operate on have a sufficient number of rows. When we do "unit" tests with a subset of the data, it all goes smoothly.
>
> Please, tell me what additional information I can provide to help fix this.
It sounds like you're bulk-loading rows, and not updating the table statistics before running a query. The pause may give autovacuum time to find and analyze the table so you get the correct query plan. I'd suggest you modify your script to add EXPLAIN ANALYZE to your query and then run it with and without the pause. Probably you'll find one is doing a full table scan, and the other is using an index.
More than likely you will need to add an "ANALYZE table_name;" into your script in place of the pause
Greg Clough
Senior Technology Engineer
O | +44 (0) 20 7665 9787
M | +44 (0) 7803 142 292
Ipreo
Castle House | 37-35 Paul St | London EC2A 4LS
--