Search Postgresql Archives

Re: Weird performance difference

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 2017-10-20 16:38, Tom Lane wrote:
Israel Brewster <israel@xxxxxxxxxxxxxx> 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


--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux