Interesting to hear that 10MB is "a significant amount of memory".
A database is not a web server, which is optimized for serving lots of short-lived connections. A PostgreSQL connection loads cached catalog data for efficiency.
That is why you use a connection pool, so that all your short database requests are handled by a small number of ...
The cache stores blocks read from from disk. A single block contains one or more rows from a table.
As both queries read the same data, they request the same blocks. So yes, the second query will be reading the blocks from the cache.
A side effect of this command is that you take an exclusive lock on the table:
ALTER TABLE DISABLE trigger ALL;
The manual on the DISABLE TRIGGER clause:
This command acquires a SHARE ROW EXCLUSIVE lock.
About SHARE ROW EXCLUSIVE:
[...] This mode protects a table against concurrent data changes, and
is self-exclusive so that only one session can hold it ...
There can be many reasons for the switched query plan. A very bad plan typically indicates inaccurate column statistics and / or cost constants. It starts with the bad estimate for the index scan on product_tracking_feed_gid_idx:
(cost=0.00..2183.11 rows=71806 width=0) (actual time=124.666..124.666 rows=1799676 loops=2)
Produces many more rows than Postgres ...
First, and most prominently, the order of columns in your multicolumn index is backwards:
[SharedOrder] (order_id, shared_to_owner)
You need an index on "SharedOrder" (shared_to_owner, order_id) for this query. See:
Is a composite index also good for queries on the first field?
You may or may not need the one you have now (...
Yes it would help performance because you table size will be reduced a lot.
Here are the possible benefits:
Auto Vacuum/analyze will be faster
Your full table scans will be faster
Your indexes on other columns will stay the same size.
Downside is you have to maintain two tables and total size of table will be a little bit bigger (due to id column in ...
you can take a look here https://severalnines.com/database-blog/overview-caching-postgresql as you can see, as long as the pages retrieved from the first SELECT have all the data in the shared_buffers cache it will use it.
No, you can only have a single statement executing at the same time on a PostgreSQL connections.
PostgreSQL v14 introduces pipelining of SQL statements, but that still means that one statement will execute after the other. Besides, I doubt that Npgsql already has support for that.
Use several database connections.
Incorrect volatility labels can prevent function inlining. See:
Can declaring function volatility IMMUTABLE harm performance?
Choose the correct volatility setting. (Leave it at its default VOLATILE if in doubt.)
Your case seems to have a superposition of multiple problems. Hard to diagnose, especially with the limited, obfuscated information we have. ...