BUG #15008: Need a pause
BUG #15008: Need a pause
От:
PG Bug reporting form <noreply@postgresql.org>
Дата:
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.
Re: BUG #15008: Need a pause
От:
Laurent Martelli <martellilaurent@gmail.com>
Дата:
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
--
RE: BUG #15008: Need a pause
От:
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
