Have you tried to set the instance running on GCP to have similar
shared_buffers as the AWS database ?
What you described has a much lower cache hit rate on GCS and 2X the
shared buffers on AWS which could well explain much of the difference
in execution times.
DETAILS:
Query explain for Postgres
...
* Remove `LIMIT` to prevent any non-deterministic behaviors
This seems counterproductive, as for example PostgreSQL has special
handling of "fast start" queries which is triggered by presence of
LIMIT or OFFSET, so this will miss some optimisations.
Also,it is not like removing LIMIT is some
In the original example it looks like using the index (and not running
a parallel query) is what made the query slow
The fast version was brute-force sequscan(s) + sort with 3 parallel
backends (leader + 2 workers) sharing the work.
On Tue, Mar 2, 2021 at 10:42 PM David Rowley wrote:
>
> On Wed
you can play around various `enable_*` flags to see if disabling any
of these will *maybe* yield the plan you were expecting, and then
check the costs in EXPLAIN to see if the optimiser also thinks this
plan is cheaper.
On Mon, Mar 22, 2021 at 6:29 PM Chris Stephens wrote:
>
> we are but i was h
erial=false;" produces an efficient plan. good to know there
> are *some* knobs to turn when the optimizer comes up with a bad plan. would
> be awesome if you could lock that plan into place w/out altering the variable.
>
> thanks for the help Hannu!
>
> On Mon, Mar 22, 20
Are you issuing "tens of reads and tens of updates/ inserts" for your
ACID transaction individually from SQL client, or have you packaged
them as a single database function ?
Using the function can be much faster, as it eliminates all the
command latencies between the client and the server.
Cheer
If there are unremovable rows it usually also means that index-only scan
degrades to index-scan-with-visibility-checks-in-tables.
I think the ask is to be able to remove the recently dead rows that are not
visible in any current snapshot and can never become visible to any future
snapshot,
Somethi
You may be interested in a patch "Adding pg_dump flag for parallel
export to pipes"[1] which allows using pipes in directory former
parallel dump and restore.
There the offsets are implicitly taken care of by the file system.
[1]
https://www.postgresql.org/message-id/CAH5HC97p4kkpikar%2BswuC0Lx4Y
