> 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