Re: BUG #15008: Need a pause - Mailing list pgsql-bugs

From Laurent Martelli
Subject Re: BUG #15008: Need a pause
Date
Msg-id CAP-_FZFox2TfXP8pTkmK--7UoAw6FFT6ksXPp_GpmVTTrY15fg@mail.gmail.com
Whole thread Raw
In response to RE: BUG #15008: Need a pause  (Greg Clough <greg.clough@ipreo.com>)
List pgsql-bugs
You are absolutely right !

Sorry for the noise here. Next time, I'll post on the user ML first.

Cheers,
Laurent

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



--

pgsql-bugs by date:

Previous
From: Robert Haas
Date:
Subject: Re: [HACKERS] [BUGS] BUG #14825: enum type: unsafe use?
Next
From: Tom Lane
Date:
Subject: Re: [BUGS] Improper const-evaluation of HAVING with grouping sets and subquery pullup