Re: How to improve the performance of my SQL query？
On 2023-07-26 15:46:16 +0800, gzh wrote: > SET enable_seqscan TO off; [...] > -> Parallel Bitmap Heap Scan on tbl_sha > (cost=92112.45..2663789.14 rows=800650 width=18) (actual > time=260.540..21442.169 rows=804500 loops=3) > Recheck Cond: (ms_cd = 'MLD009'::bpchar) > Rows Removed by Index Recheck: 49 > Filter: (etrys = '0001'::bpchar) > Rows Removed by Filter: 295500 > Heap Blocks: exact=13788 lossy=10565 > -> Bitmap Index Scan on index_search_04_mscd_cdate > (cost=0.00..91632.06 rows=3402599 width=0) (actual time=249.718..249.718 > rows=330 loops=1) > Index Cond: (ms_cd = 'MLD009'::bpchar) So now it's using index_search_04_mscd_cdate which contains only ms_cd (and - judging from the name, other fields not relevant to this query), but it still doesn't use index_search_01 which would fit the query exactly. I can understand that Postgres prefers a sequential scan over an index scan (the number of matching rows is about 10% of the total table size which is a lot), but why would it prefer a less specific index to a more specific one? Can you get Postgres to use that index at all? Find a combination of ms_cd and etrys which doesn't cover millions of rows and try that. Also try lowering random_page_cost. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: How to improve the performance of my SQL query？
> On 29 Jul 2023, at 10:59, Peter J. Holzer wrote: > > On 2023-07-26 15:46:16 +0800, gzh wrote: >> SET enable_seqscan TO off; > [...] >>-> Parallel Bitmap Heap Scan on tbl_sha >> (cost=92112.45..2663789.14 rows=800650 width=18) (actual >> time=260.540..21442.169 rows=804500 loops=3) >> Recheck Cond: (ms_cd = 'MLD009'::bpchar) >> Rows Removed by Index Recheck: 49 >> Filter: (etrys = '0001'::bpchar) >> Rows Removed by Filter: 295500 >> Heap Blocks: exact=13788 lossy=10565 >> -> Bitmap Index Scan on index_search_04_mscd_cdate >> (cost=0.00..91632.06 rows=3402599 width=0) (actual time=249.718..249.718 >> rows=330 loops=1) >>Index Cond: (ms_cd = 'MLD009'::bpchar) > > So now it's using index_search_04_mscd_cdate which contains only ms_cd > (and - judging from the name, other fields not relevant to this query), > but it still doesn't use index_search_01 which would fit the query > exactly. I can understand that Postgres prefers a sequential scan over > an index scan (the number of matching rows is about 10% of the total > table size which is a lot), but why would it prefer a less specific > index to a more specific one? > > Can you get Postgres to use that index at all? > > Find a combination of ms_cd and etrys which doesn't cover millions of > rows and try that. > > Also try lowering random_page_cost. Wasn’t this an RDS server with just 4GB of memory? How large are those multi-column indices? Perhaps they don’t (all) fit into available cache memory and the server decided to use the one that it had cached? I’m frankly not at all certain how the server would behave around such resource shortage situations, but I suppose loading an uncached index into cache could get a higher cost than using a less optimal (costlier) index that’s already cached. Regarding lowering random_page_cost; If your index files are on SSD storage, lowering that sufficiently (to a realistic value) could then sufficiently lower the cost of loading that uncached index into memory, evicting the index it was using in above plan to make room (unless other active sessions are using it). Alban Hertroys -- There is always an exception to always.
Timeout in Logical Replication
Logical replication from PG 15.3 to 15.2 has been running without problems until a few days ago. Now the subscriber shows repeated messages like: ``` 2023-07-29 08:25:04.523 UTC [26] LOG: checkpoint complete: wrote 8692 buffers (53.1%); 0 WAL file(s) added, 1 removed, 14 recycled; write=269.921 s, sync=0.485 s, total=270.438 s; sync files=37, longest=0.224 s, average=0.014 s; distance=230568 kB, estimate=436766 kB 2023-07-29 08:25:34.550 UTC [26] LOG: checkpoint starting: time 2023-07-29 08:27:55.699 UTC [142] ERROR: could not receive data from WAL stream: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. 2023-07-29 08:27:55.702 UTC [159] LOG: logical replication apply worker for subscription "" has started 2023-07-29 08:27:55.706 UTC [1] LOG: background worker "logical replication worker" (PID 142) exited with exit code 1 ``` And the publisher shows repeated messages like: ``` 2023-07-29 08:24:50.341 UTC [530982] STATEMENT: START_REPLICATION SLOT "" LOGICAL 37D1/1E0DD9A0 (proto_version '3', publication_names '""') 2023-07-29 08:27:36.956 UTC [530982] LOG: terminating walsender process due to replication timeout 2023-07-29 08:27:36.956 UTC [530982] CONTEXT: slot "", output plugin "pgoutput", in the change callback, associated LSN 37D0/F9E8C2E8 ``` I can connect using `psql` from either node back to the other. As far as I have been able to determine no routing or firewall changes have been made. Reading other similar reports suggests that deleting and recreating the sub will fix the problem, but I'd like to understand/avoid it. Any suggestions on how to track this down would be appreciated. -
Re: How to get an md5/sha256 hash of a really large object in psql?
> On 29/07/2023 08:42 CEST Alex Shan <3341...@gmail.com> wrote:
>
> In my DB I have a large object over 4GB in size.
> I need to get its MD5 or SHA256 from within psql query, i.e. without
> exporting it to FS first.
>
> “SELECT md5(lo_get(loid));” doesnt work — “large object is too large”.
>
> Is there any other way to do it?
Is plpython3u [1] an option for you? In that case you can use Python's hashlib
in a custom function and feed every page from pg_largeobject to a selected hash
function.
For example:
BEGIN;
CREATE EXTENSION plpython3u;
CREATE OR REPLACE FUNCTION lo_hash(loid oid, name text)
RETURNS bytea
LANGUAGE plpython3u
AS $$
import hashlib
hash = hashlib.new(name)
# Check if large object exists.
plan = plpy.prepare("""
SELECT
FROM pg_largeobject_metadata
WHERE oid = $1
""", ['oid'])
rv = plpy.execute(plan, [loid])
if rv.nrows() == 0:
raise ValueError(f"large object {loid} does not exist")
# Get all pages (possibly zero).
plan = plpy.prepare("""
SELECT data
FROM pg_largeobject
WHERE loid = $1
ORDER BY pageno
""", ['oid'])
pages = plpy.cursor(plan, [loid])
for page in pages:
hash.update(page['data'])
return hash.digest()
$$;
COMMIT;
Testing with 65 KiB null bytes:
BEGIN;
SELECT
lo_from_bytea(0, decode(repeat('00', 1 << 16), 'hex')) AS test_loid
\gset
SELECT
loid,
count(*) AS n_pages,
sum(length(data)) AS n_bytes
FROM pg_largeobject
WHERE loid = :test_loid
GROUP BY loid;
loid | n_pages | n_bytes
+-+-
365958 | 32 | 65536
(1 row)
SELECT
:test_loid AS loid,
lo_hash(:test_loid, 'md5') AS md5;
loid |md5
+
365958 | \xfcd6bcb56c1689fcef28b57c22475bad
(1 row)
SELECT
:test_loid AS loid,
lo_hash(:test_loid, 'sha256') AS sha256;
loid | sha256
+
365958 |
\xde2f256064a0af797747c2b97505dc0b9f3df0de4f489eac731c23ae9ca9cc31
(1 row)
ROLLBACK;
Verifying the hashes:
$ head -c65536 /dev/zero | md5sum
fcd6bcb56c1689fcef28b57c22475bad -
$ head -c65536 /dev/zero | sha256sum
de2f256064a0af797747c2b97505dc0b9f3df0de4f489eac731c23ae9ca9cc31 -
[1] https://www.postgresql.org/docs/15/plpython.html
--
Erik
