Use of inefficient index in the presence of dead tuples
I am encountering an odd problem where Postgres will use the wrong index,
particularly if the table
has some dead tuples. The database affected is running 12.6, but I can also
reproduce with 16.3.
To reproduce:
(1) Disable autovacuum. This is just so we can induce a scenario where there
are lots of dead tuples.
(2) Set up schema. It's important to create the index before insertion, in
order to provoke a
situation where the indexes have dead tuples:
CREATE TABLE outbox_batches (
id textNOT NULL,
receiver textNOT NULL,
created_at timestamp without time zone DEFAULT now() NOT NULL,
PRIMARY KEY (receiver, id)
);
CREATE INDEX outbox_batches_on_receiver_and_created_at
ON outbox_batches (receiver, created_at DESC);
(3) Insert 5M rows of dummy data. Note that we are using UUIDs here for the
purposes of testing; in
my real database, I use much shorter unique IDs.
INSERT INTO outbox_batches (receiver, id)
SELECT 'dummy', uuid_generate_v4()
FROM (SELECT * FROM generate_series(1, 500, 1)) AS foo;
(4) Then ensure all tuples are dead except one:
DELETE FROM outbox_batches;
INSERT INTO outbox_batches (receiver, id) VALUES ('dummy', 'test');
(5) Analyze:
ANALYZE outbox_batches;
(6) You should now have 5m dead rows and 1 live row:
SELECT n_live_tup, n_dead_tup FROM pg_stat_user_tables WHERE relname =
'outbox_batches';
┌┬┐
│ n_live_tup │ n_dead_tup │
├┼┤
│ 1 │500 │
└┴┘
We also observe that the outbox_batches_pkey index is 454 MB, and the
outbox_batches_on_receiver_and_created_at is 31 MB.
(7) Try the following query:
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS, TIMING, SETTINGS, SUMMARY)
SELECT id FROM outbox_batches
WHERE receiver = 'dummy'
AND id = 'test';
Here's the query plan:
Index Scan using outbox_batches_on_receiver_and_created_at on
public.outbox_batches (cost=0.38..8.39 rows=1 width=5) (actual
time=0.426..984.038 rows=1 loops=1)
Output: id
Index Cond: (outbox_batches.receiver = 'dummy'::text)
Filter: (outbox_batches.id = 'test'::text)
Buffers: shared hit=3948 read=60742 dirtied=60741 written=30209
Settings: work_mem = '32MB'
Query Identifier: -2232653838283363139
Planning:
Buffers: shared hit=18 read=3
Planning Time: 1.599 ms
Execution Time: 984.082 ms
This query is reading 60K buffers even though it only needs to read a single
row. Notice in particular the
use of the index outbox_batches_on_receiver_and_created_at, even though
outbox_batches_pkey would be
a much better choice. We know this because if we drop the first index:
Index Only Scan using outbox_batches_pkey on public.outbox_batches
(cost=0.50..8.52 rows=1 width=5) (actual time=2.067..2.070 rows=1 loops=1)
Output: id
Index Cond: ((outbox_batches.receiver = 'dummy'::text) AND
(outbox_batches.id = 'test'::text))
Heap Fetches: 1
Buffers: shared hit=1 read=4
Settings: work_mem = '32MB'
Query Identifier: -2232653838283363139
Planning:
Buffers: shared hit=5 dirtied=1
Planning Time: 0.354 ms
Execution Time: 2.115 ms
This is also the index that's used in the normal case when there are no dead
tuples at all.
Interestingly, the cost of an index only scan on outbox_batches_pkey is 8.52,
whereas the other is
8.39. Is this because it considers the number of index pages? I've tried
adjusting the various cost
and memory settings, but they have no effect.
In this test, we created 5M dead tuples. However, for me it also reproduces
with just 1,000 rows.
For such a small table, the performance degradation is minimal, but it
increases as more and more
tuples are deleted.
In a production environment, we have rows being constantly deleted at a high
rate, leaving a table
that often has very few live tuples, and often 500K+ dead tuples before
autovacuum can kick in. Here
I am consistently seeing the wrong index used, leading to poor performance.
The autovacuum settings ar aggressive, but for whatever reason it is not
keeping up. We also have
long-running transactions that sometimes cause the xmin to hang back for a
while, preventing
vacuums from helping.
All of that said, I would rather Postgres choose the right index than spend a
lot of time optimizing
vacuums.
Here's my full server config:
https://gist.github.com/atombender/54207d473e415fab26fc59751a22feca.
Re: Use of inefficient index in the presence of dead tuples
On Tue, 2024-05-28 at 10:00 +0200, Alexander Staubo wrote: > I am encountering an odd problem where Postgres will use the wrong index, > particularly if the table > has some dead tuples. The database affected is running 12.6, but I can also > reproduce with 16.3. > > To reproduce: > [create a table with a larger index on "id" and "receiver" and a smaller on > "receiver" and "created_at", then delete all but one row and ANALYZE] > > (7) Try the following query: > > EXPLAIN (ANALYZE, VERBOSE, BUFFERS, COSTS, TIMING, SETTINGS, SUMMARY) > SELECT id FROM outbox_batches > WHERE receiver = 'dummy' > AND id = 'test'; > > Here's the query plan: > > Index Scan using outbox_batches_on_receiver_and_created_at on > public.outbox_batches (cost=0.38..8.39 rows=1 width=5) (actual > time=0.426..984.038 rows=1 loops=1) > Output: id > Index Cond: (outbox_batches.receiver = 'dummy'::text) > Filter: (outbox_batches.id = 'test'::text) > Buffers: shared hit=3948 read=60742 dirtied=60741 written=30209 > Settings: work_mem = '32MB' > Query Identifier: -2232653838283363139 > Planning: > Buffers: shared hit=18 read=3 > Planning Time: 1.599 ms > Execution Time: 984.082 ms > > This query is reading 60K buffers even though it only needs to read a single > row. Notice in particular the > use of the index outbox_batches_on_receiver_and_created_at, even though > outbox_batches_pkey would be > a much better choice. We know this because if we drop the first index: > > Index Only Scan using outbox_batches_pkey on public.outbox_batches > (cost=0.50..8.52 rows=1 width=5) (actual time=2.067..2.070 rows=1 loops=1) > Output: id > Index Cond: ((outbox_batches.receiver = 'dummy'::text) AND > (outbox_batches.id = 'test'::text)) > Heap Fetches: 1 > Buffers: shared hit=1 read=4 > Settings: work_mem = '32MB' > Query Identifier: -2232653838283363139 > Planning: > Buffers: shared hit=5 dirtied=1 > Planning Time: 0.354 ms > Execution Time: 2.115 ms > > This is also the index that's used in the normal case when there are no dead > tuples at all. > > Interestingly, the cost of an index only scan on outbox_batches_pkey is 8.52, > whereas the other is > 8.39. Is this because it considers the number of index pages? I've tried > adjusting the various cost > and memory settings, but they have no effect. ANALYZE considers only the live rows, so PostgreSQL knows that the query will return only few results. So it chooses the smaller index rather than the one that matches the WHERE condition perfectly. Unfortunately, it has to wade through all the deleted rows, which is slow. But try to execute the query a second time, and it will be much faster. PostgreSQL marks the index entries as "dead" during the first execution, so the second execution won't have to look at the heap any more. See https://www.cybertec-postgresql.com/en/killed-index-tuples/ > In this test, we created 5M dead tuples. However, for me it also reproduces > with just 1,000 rows. > For such a small table, the performance degradation is minimal, but it > increases as more and more > tuples are deleted. > > In a production environment, we have rows being constantly deleted at a high > rate, leaving a table > that often has very few live tuples, and often 500K+ dead tuples before > autovacuum can kick in. Here > I am consistently seeing the wrong index used, leading to poor performance. > > The autovacuum settings ar aggressive, but for whatever reason it is not > keeping up. We also have > long-running transactions that sometimes cause the xmin to hang back for a > while, preventing > vacuums from helping. > > All of that said, I would rather Postgres choose the right index than spend a > lot of time optimizing > vacuums. I understand your pain, but your use case is somewhat unusual. What I would consider in your place is a) running an explicit VACUUM after you delete lots of rows or b) using partitioning to get rid of old data I don't know how the PostgreSQL optimizer could be improved to take dead rows into account. Yours, Laurenz Albe
Re: Use of inefficient index in the presence of dead tuples
On 28 May 2024, at 13:02, Laurenz Albe wrote: > ANALYZE considers only the live rows, so PostgreSQL knows that the query will > return only few results. So it chooses the smaller index rather than the one > that matches the WHERE condition perfectly. > > Unfortunately, it has to wade through all the deleted rows, which is slow. Sounds like the planner _should_ take the dead tuples into account. I’m surprised there are no parameters to tweak to make the planner understand that one index is more selective even though it is technically larger. > But try to execute the query a second time, and it will be much faster. > PostgreSQL marks the index entries as "dead" during the first execution, so > the > second execution won't have to look at the heap any more. Of course. It’s still not _free_; it’s still trawling through many megabytes of dead data, and going through the shared buffer cache and therefore competing with other queries that need shared buffers. > I understand your pain, but your use case is somewhat unusual. I don’t think rapidly updated tables is an unusual use of Postgres, nor is the problem of long-running transaction preventing dead tuple vacuuming. > What I would consider in your place is > a) running an explicit VACUUM after you delete lots of rows or The rows are deleted individually. It’s just that there are many transactions doing it concurrently. > b) using partitioning to get rid of old data Partitioning will generate dead tuples in the original partition when tuples are moved to the other partition, so I’m not sure how that would help? I did explore a solution which is my “plan B” — adding a “done” column, then using “UPDATE … SET done = true” rather than deleting the rows. This causes dead tuples, of course, but then adding a new index with a “… WHERE NOT done” filter fixes the problem by forcing the query to use the right index. However, with this solution, rows will still have to be deleted *sometime*, so this just delays the problem. But it would allow a “batch cleanup”: “DELETE … WHERE done; VACUUM” in one fell swoop.
Re: Use of inefficient index in the presence of dead tuples
On Tue, May 28, 2024, 07:21 Alexander Staubo wrote: > > > I did explore a solution which is my “plan B” — adding a “done” column, > then using “UPDATE … SET done = true” rather than deleting the rows. This > causes dead tuples, of course, but then adding a new index with a “… WHERE > NOT done” filter fixes the problem by forcing the query to use the right > index. However, with this solution, rows will still have to be deleted > *sometime*, so this just delays the problem. But it would allow a “batch > cleanup”: “DELETE … WHERE done; VACUUM” in one fell swoop. > If you incorporate partitions into this, the final removal of the soft deleted rows becomes and truncate or a drop instead of a delete. David J.
Memory issues with PostgreSQL 15
Hi all, We migrated from PostgreSQL 9.4 to PostgreSQL 15 a while ago. Since then, we have a lot of memory issues in our QA environment (which is a bit tense in resources). We did not have these problems before the migration, and we do not have them in our production environment, which has a lot more memory. So, it is not super critical for us, but I would still like to understand better how we can improve our configuration. Our PostgreSQL version is "PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit". The database server is a dedicated server with 15 GB RAM (and 4 cores, if this matters). We used the following settings: shared_buffers = 4GB work_mem = 4MB After a while, we saw the following error in the logs: <2024-05-20 12:01:03 CEST - > LOG: could not fork autovacuum worker process: Cannot allocate memory However, according to "free", a lot of memory was available: # free -m totalusedfree shared buff/cache available Mem: 158824992 4634195 104276365 Swap: 1999 2711728 Our Grafana charts showed a slow increase in memory consumption until it plateaus at 4.66 GB. We also found the following error: <2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize shared memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on device I thought this could all be related to our "shared_buffers" setting, so I increased it to 8 GB. This almost immediately (after a few minutes) gave me these errors: <2024-05-27 11:45:59 CEST - > ERROR: out of memory <2024-05-27 11:45:59 CEST - > DETAIL: Failed on request of size 201088574 in memory context "TopTransactionContext". ... <2024-05-27 11:58:02 CEST - > ERROR: out of memory <2024-05-27 11:58:02 CEST - > DETAIL: Failed while creating memory context "dynahash". <2024-05-27 11:58:02 CEST - > LOG: background worker "parallel worker" (PID 21480) exited with exit code 1 ... <2024-05-27 12:01:02 CEST - > LOG: could not fork new process for connection: Cannot allocate memory <2024-05-27 12:01:03 CEST - > LOG: could not fork autovacuum worker process: Cannot allocate memory <2024-05-27 12:02:02 CEST - > LOG: could not fork new process for connection: Cannot allocate memory Since this seemed worse than before, I changed the setting back to 4 GB. I noticed that "free" now reports even more available memory: # free -m totalusedfree shared buff/cache available Mem: 15882 621 3202256 14940 12674 Swap: 1999 1991800 So, does the "shared_buffers" setting have the opposite effect than I though? If I correctly remember similar discussions years ago, the database needs both "normal" and shared memory. By increasing the "shared_buffers" to 8 GB, I may have deprived it of "normal" memory. On the other hand, I would have expected the remaining 7 GB to still be enough. At this point, I am out of ideas. I clearly seem to misunderstand how the database manages its memory. This may have changed between 9.4 and 15, so my prior knowledge may be useless. I definitely need some help. ☹ Thanks in advance, Christian -- SUPPORT: For any issues, inquiries, or assistance, please contact our support team at supp...@wsd.com. Our dedicated team is available to help you and provide prompt assistance. CONFIDENTIALITY NOTICE: This email and any attachments are confidential and intended solely for the use of the individual or entity to whom it is addressed. If you have received this email in error, please notify the sender immediately and delete it from your system.
Pgpool with high availability
HI Team, "I'm encountering challenges while configuring Pgpool with high availability. The initial setup is completed, and Pgpool is operational on a single node, functioning without issues. However, upon attempting to start Pgpool on any additional nodes, particularly node 2, it becomes immediately unreachable. I'm seeking assistance to address this issue. My setup consists of three nodes, each hosting both PostgreSQL and Pgpool services." Thanks Vijay
Re: Pgpool with high availability
Hi Vijay Can you please share the error log, as from the description its hard to figure out the issue causing this problem. Regards Kashif Zeeshan Bitnine Global On Tue, May 28, 2024 at 9:42 PM vijay patil wrote: > > HI Team, > > "I'm encountering challenges while configuring Pgpool with high > availability. The initial setup is completed, and Pgpool is operational on > a single node, functioning without issues. However, upon attempting to > start Pgpool on any additional nodes, particularly node 2, it becomes > immediately unreachable. > > I'm seeking assistance to address this issue. My setup consists of three > nodes, each hosting both PostgreSQL and Pgpool services." > > > Thanks > > Vijay >
Re: Memory issues with PostgreSQL 15
Hi Christian: On Tue, 28 May 2024 at 18:40, Christian Schröder wrote: > <2024-05-21 11:34:46 CEST - mailprocessor> ERROR: could not resize shared > memory segment "/PostgreSQL.2448337832" to 182656 bytes: No space left on > device This hints at some shm function getting an ENOSPC: Coupled with... > I thought this could all be related to our "shared_buffers" setting, so I > increased it to 8 GB. This almost immediately (after a few minutes) gave me > these errors: A faster fail when increasing it I would start by checking your IPC shared memory limits are ok, especially if you upgraded something in the OS when going from 9 to 15, which seems likely. IIRC in linux you can read them in /proc/sys/kernel/shm*, and they were configured via sysctl. Francisco Olarte.
Re: Pgpool with high availability
On 5/28/24 1:31 AM, vijay patil wrote: HI Team, "I'm encountering challenges while configuring Pgpool with high availability. The initial setup is completed, and Pgpool is operational on a single node, functioning without issues. However, upon attempting to start Pgpool on any additional nodes, particularly node 2, it becomes immediately unreachable. And how we are supposed to arrive at an answer with essentially no information provided? Need: 1) Configuration for initial setup. 2) A more detailed explanation of what "... upon attempting to start Pgpool on any additional nodes" means? Include configuration changes. 3) The error messages. 4) Where the nodes are located? I'm seeking assistance to address this issue. My setup consists of three nodes, each hosting both PostgreSQL and Pgpool services." Thanks Vijay -- Adrian Klaver adrian.kla...@aklaver.com
Re: Long running query causing XID limit breach
On Mon, May 27, 2024 at 2:50 PM Laurenz Albe wrote: > > > > But again for HA , in case primary down we should not be in big lag > for the standby > > > and thus we want the standby also with minimal lag. And as you > mentioned there will > > > never be incorrect results but at amx it will be query cancellation, > so I was thinking, > > > if it's fine to just keep the "hot_feedback_standby" as OFF and let the > > > max_standby_streaming_delay set as it is like 14 sec. Let me know your > thoughts. > > > You cannot have it. > Let me repeat: you cannot have it. > > The only way you can have no delay in replication AND no canceled queries > is > if you use two different standby servers with different settings for > "max_standby_streaming_delay". One of the server is for HA, the other for > your long-running queries. > > When you suggest having different max_standby_streaming_delay for first replica (say 10 sec for High availability) and second replica(say -1 for long running queries). Do you also suggest keeping "hot_feedback_standby" as "OFF" for all the three instances i.e. master and both the replicas? Also OP has added a few other parameters as below, do you think these should be needed? I think the master and first replica should have the same set up because in case of any disaster to master the first replica should be able to take the place of master. Master/Primary First Replica/Standby for High Availability Second Replica for Reporting hot_standby_feedback=ON hot_standby_feedback=ON hot_standby_feedback=OFF max_standby_streaming_delay=10 sec max_standby_streaming_delay=10 sec max_standby_streaming_delay=-1 (Infinite) statement_timeout = "2hrs" statement_timeout="2hrs" No statement_timeout i.e. infinite idle_in_transaction_session_timeout=10minutes idle_in_transaction_session_timeout=10minutes No idle_in_transaction_session_timeout i.e. infinite autovacuum_freeze_max_age=100M autovacuum_freeze_max_age=100M autovacuum_freeze_max_age=100M Log_autovacuum_min_duration=0 Log_autovacuum_min_duration=0 Log_autovacuum_min_duration=0
tds_fdw >> Install Foreign data Wrapper on EDB Postgres to connect to SQL server database
Installation of Foreign data Wrapper on EDB Postgres to connect to SQL server
database is not working.
https://github.com/tds-fdw/tds_fdw/issues/357
Please advise if this has been encountered or a workaround is available.
OS:
NAME="Oracle Linux Server"
VERSION="7.9"
ID="ol"
ID_LIKE="fedora"
VARIANT="Server"
VARIANT_ID="server"
VERSION_ID="7.9"
PRETTY_NAME="Oracle Linux Server 7.9"
ANSI_COLOR="0;31"
CPE_NAME="cpe:/o:oracle:linux:7:9:server"
HOME_URL=https://linux.oracle.com/
BUG_REPORT_URL=https://github.com/oracle/oracle-linux
ORACLE_BUGZILLA_PRODUCT="Oracle Linux 7"
ORACLE_BUGZILLA_PRODUCT_VERSION=7.9
ORACLE_SUPPORT_PRODUCT="Oracle Linux"
ORACLE_SUPPORT_PRODUCT_VERSION=7.9
Version of tds_fdw:
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--+-++--
edb_dblink_libpq | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper
for PostgreSQL
edb_dblink_oci | 1.0 | pg_catalog | EnterpriseDB Foreign Data Wrapper
for Oracle
edbspl | 1.0 | pg_catalog | EDB-SPL procedural language
pldbgapi | 1.1 | pg_catalog | server-side support for debugging
PL/pgSQL functions
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(5 rows)
PostgreSQL Version:
postgres=# SELECT version();
version
--
PostgreSQL 13.13 (EnterpriseDB Advanced Server 13.13.19) on
x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44),
64-bit
(1 row)
Issue Details:
tds_fdw install steps performed:
yum install install edb-as13-server edb-as13-server-libs edb-as13-server-devel
yum install -y gcc make wget epel-release freetds freetds-devel
dnf install redhat-rpm-config
subscription-manager repos --enable rhel-7-server-optional-rpms \
--enable rhel-server-rhscl-7-rpms \
--enable rhel-7-server-devtools-rpms
yum install llvm-toolset-7-clang-analyzer llvm-toolset-7-clang-tools-extra
llvm-toolset-7 llvm-toolset-7-clang llvm-toolset-7-clang-libs
llvm-toolset-7-llvm-libs
export TDS_FDW_VERSION="2.0.3"
wget https://github.com/tds-fdw/tds_fdw/archive/refs/tags/v2.0.3.tar.gz -O
tds_fdw-${TDS_FDW_VERSION}.tar.gz
tar -xvzf tds_fdw-${TDS_FDW_VERSION}.tar.gz
cd tds_fdw-${TDS_FDW_VERSION}
PATH=/usr/edb/as13/bin:$PATH
make USE_PGXS=1
PATH=/usr/edb/as13/bin:$PATH
make USE_PGXS=1 install
Logs:
[root@hostip ~]# yum install install edb-as13-server edb-as13-server-libs
edb-as13-server-devel\
Loaded plugins: rhnplugin\
This system is not registered with Spacewalk server.\
You can use rhn_register to register.\
Spacewalk support will be disabled.\
Repository mc-ol7-base-x86_64 is listed more than once in the configuration\
Repository mc-ol7-layered-x86_64 is listed more than once in the configuration\
Repository mc-ol7_tsm-8.1.13_x86_64 is listed more than once in the
configuration\
Repository mc-ol7-chef_client_15-x86_64 is listed more than once in the
configuration\
No package install available.\
Package edb-as13-server-13.13.19-1.el7.x86_64 already installed and latest
version\
Package edb-as13-server-libs-13.13.19-1.el7.x86_64 already installed and latest
version\
Package edb-as13-server-devel-13.13.19-1.el7.x86_64 already installed and
latest version\
Nothing to do\
[root@hostip ~]#\
[root@hostip ~]# yum install -y gcc make wget epel-release freetds
freetds-devel\
Loaded plugins: rhnplugin\
This system is not registered with Spacewalk server.\
You can use rhn_register to register.\
Spacewalk support will be disabled.\
Repository mc-ol7-base-x86_64 is listed more than once in the configuration\
Repository mc-ol7-layered-x86_64 is listed more than once in the configuration\
Repository mc-ol7_tsm-8.1.13_x86_64 is listed more than once in the
configuration\
Repository mc-ol7-chef_client_15-x86_64 is listed more than once in the
configuration\
Package gcc-4.8.5-44.0.3.el7.x86_64 already installed and latest version\
Package 1:make-3.82-24.el7.x86_64 already installed and latest version\
Package wget-1.14-18.el7_6.1.x86_64 already installed and latest version\
Package epel-release-7-14.noarch already installed and latest version\
Package freetds-1.3.3-1.el7.x86_64 already installed and latest version\
Package freetds-devel-1.3.3-1.el7.x86_64 already installed and latest version\
Nothing to do\
[root@hostip ~]#\
[root@hostip ~]# dnf install redhat-rpm-config\
-bash: dnf: command not found\
[root@hostip ~]#\
[root@hostip ~]# subscription-manager repos --enable
rhel-7-server-optional-rpms \ --enable rhel-server-rhscl-7-rpms \ --enable
rhel-7-server-devtools-rpms\
-bash: subscription-
Re: tds_fdw >> Install Foreign data Wrapper on EDB Postgres to connect to SQL server database
2024年5月29日(水) 6:10 Sumit Kochar : > > Installation of Foreign data Wrapper on EDB Postgres to connect to SQL server > database is not working. > > > > https://github.com/tds-fdw/tds_fdw/issues/357 > > > > Please advise if this has been encountered or a workaround is available. (...) > [root@hostip tds_fdw-2.0.3]# make USE_PGXS=1 install > > /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes > -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 > -I./include/ -fvisibility=hidden -I. -I./ -I/usr/edb/as13/include/server > -I/usr/edb/as13/include/internal -I/usr/libexec/edb-icu66/include/ > -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c > -o src/tds_fdw.bc src/tds_fdw.c > > make: /opt/rh/llvm-toolset-7/root/usr/bin/clang: Command not found IIRC you'll need to find and install the SCLO package for Oracle Linux 7, assuming it's available. For CentOS it's this repository: http://mirror.centos.org/centos/7/sclo/ . Regards Ian Barwick
Re: Use of inefficient index in the presence of dead tuples
Alexander Staubo writes:
> (2) Set up schema. It's important to create the index before insertion, in
> order to provoke a
> situation where the indexes have dead tuples:
> ...
> (4) Then ensure all tuples are dead except one:
> DELETE FROM outbox_batches;
> INSERT INTO outbox_batches (receiver, id) VALUES ('dummy', 'test');
> (5) Analyze:
> ANALYZE outbox_batches;
So the problem here is that the ANALYZE didn't see any of the dead rows
and thus there is no way to know that they all match 'dummy'. The cost
estimation is based on the conclusion that there is exactly one row
that will pass the index condition in each case, and thus the "right"
index doesn't look any cheaper than the "wrong" one --- in fact, it
looks a little worse because of the extra access to the visibility
map that will be incurred by an index-only scan.
I'm unpersuaded by the idea that ANALYZE should count dead tuples.
Since those are going to go away pretty soon, we would risk
estimating on the basis of no-longer-relevant stats and thus
creating problems worse than the one we solve.
What is interesting here is that had you done ANALYZE *before*
the delete-and-insert, you'd have been fine. So it seems like
somewhat out-of-date stats would have benefited you.
It would be interesting to see a non-artificial example that took
into account when the last auto-vacuum and auto-analyze really
happened, so we could see if there's any less-fragile way of
dealing with this situation.
regards, tom lane
Re: tds_fdw >> Install Foreign data Wrapper on EDB Postgres to connect to SQL server database
Hi , You need to install the LLVM toolset. 1)Check that if it is installed or not : yum list installed | grep llvm-toolset 2)If it is not installed, you can install it using the following command. sudo yum install llvm-toolset-7 Regards, Muhammad Imtiaz On Wed, 29 May 2024 at 05:27, Ian Lawrence Barwick wrote: > 2024年5月29日(水) 6:10 Sumit Kochar : > > > > Installation of Foreign data Wrapper on EDB Postgres to connect to SQL > server database is not working. > > > > > > > > https://github.com/tds-fdw/tds_fdw/issues/357 > > > > > > > > Please advise if this has been encountered or a workaround is available. > (...) > > > [root@hostip tds_fdw-2.0.3]# make USE_PGXS=1 install > > > > /opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes > -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 > -I./include/ -fvisibility=hidden -I. -I./ -I/usr/edb/as13/include/server > -I/usr/edb/as13/include/internal -I/usr/libexec/edb-icu66/include/ > -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm > -c -o src/tds_fdw.bc src/tds_fdw.c > > > > make: /opt/rh/llvm-toolset-7/root/usr/bin/clang: Command not found > > IIRC you'll need to find and install the SCLO package for Oracle Linux > 7, assuming it's > available. For CentOS it's this repository: > http://mirror.centos.org/centos/7/sclo/ . > > Regards > > Ian Barwick > > >
Re: Pgpool with high availability
Hi, Can you please provide the Pgpool logs? Additionally, in a distributed environment, ensure that Pgpool is properly configured. You can follow these documented steps to set up the Pgpool configurations pgpool.conf,pcp.conf and pool_hba.conf . Regards, Muhammad Imtiaz On Tue, 28 May 2024 at 23:01, Adrian Klaver wrote: > > > On 5/28/24 1:31 AM, vijay patil wrote: > > > > HI Team, > > > > "I'm encountering challenges while configuring Pgpool with high > > availability. The initial setup is completed, and Pgpool is operational > > on a single node, functioning without issues. However, upon attempting > > to start Pgpool on any additional nodes, particularly node 2, it becomes > > immediately unreachable. > > And how we are supposed to arrive at an answer with essentially no > information provided? > > Need: > > 1) Configuration for initial setup. > > 2) A more detailed explanation of what "... upon attempting > to start Pgpool on any additional nodes" means? Include configuration > changes. > > 3) The error messages. > > 4) Where the nodes are located? > > > > > > I'm seeking assistance to address this issue. My setup consists of three > > nodes, each hosting both PostgreSQL and Pgpool services." > > > > > > Thanks > > > > Vijay > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com > > >
