Re: Weird performance difference

On 2017-10-20 16:38, Tom Lane wrote:
> Israel Brewster <israel(at)ravnalaska(dot)net> writes:
>>> Can you send "explain" (not explain analyze) for the production
>>> server?
>
>> Not for the full query - it only just completed, after 70 minutes or
>> so, and I wasn't running under EXPLAIN ANALYZE. Running with a shorter
>> date range of only 7 days, as you suggest below:
>> https://explain.depesz.com/s/r80j <https://explain.depesz.com/s/r80j>
>
> First thing that jumps out from that is
>
>> Foreign Scan on oag_schedules (cost=100.00..128.60 rows=620 width=108)
>> (actual time=3.576..477.524 rows=79,853 loops=1)
>
> Being off by a factor of 100 at the scan level is never a good start
> for a
> join plan. Turn on use_remote_estimate (assuming these are
> postgres_fdw
> tables). Also try explicitly ANALYZE'ing the foreign tables. I do not
> believe auto-analyze will touch foreign tables ...

Thanks - the ANALYZE apparently did it. Running the FULL query (for the
entire year) now returns in slightly better time than my test machine:
https://explain.depesz.com/s/GtiM

Also, the query plan now looks similar. So now that it's working, I can
move on to optimizing. It's already been suggested that I remove the
cast to date (or index it), so I guess that's the first thing I'll try.

>
> regards, tom lane