williamdclt
11/21/2022, 4:17 PMsql
SELECT namespace, object_id, relation, userset_namespace, userset_object_id, userset_relation, created_transaction, deleted_transaction
FROM relation_tuple
WHERE created_xid IS NULL
LIMIT 1000 FOR UPDATE;
Query plan (EXPLAIN ANALYZE):
sql
Limit (cost=0.00..68.34 rows=1000 width=150) (actual time=468.112..472.134 rows=1000 loops=1)
-> LockRows (cost=0.00..479050.71 rows=7009496 width=150) (actual time=468.111..472.023 rows=1000 loops=1)
-> Seq Scan on relation_tuple (cost=0.00..408955.75 rows=7009496 width=150) (actual time=468.097..468.493 rows=1000 loops=1)
Filter: (created_xid IS NULL)
Rows Removed by Filter: 2947393
Planning Time: 0.105 ms
Execution Time: 472.248 ms
The index on created_xid IS NULL
isn't used 🤔 Even after an ANALYZE
. No idea why.CREATE INDEX CONCURRENTLY ix_backfill_tuple_temp_2 ON public.relation_tuple USING btree (created_xid) WHERE created_xid IS NOT NULL;
To no availcreated_xid IS NULL
FOR UPDATE
doesn't change anythingJake
11/21/2022, 5:01 PMwilliamdclt
11/21/2022, 5:06 PMJake
11/21/2022, 5:07 PMwilliamdclt
11/21/2022, 5:07 PMSELECT created_xid
but not if selecting anything elseJake
11/21/2022, 5:08 PMwilliamdclt
11/21/2022, 5:09 PMJake
11/21/2022, 5:10 PMwilliamdclt
11/21/2022, 5:13 PMcreated_xid IS NULL
are more-or-less random in a sequential walk, so it's indeed theoretically cheaper to go for a seq walk (see SO)
- everytime we do this SELECT, PG walks through the rows in the same order
- because we set the selected rows to NOT NULL, it takes PG longer and longer to find IS NULL rows in its sequential walkJake
11/21/2022, 5:14 PMorder by rand()
or somethingwilliamdclt
11/21/2022, 5:14 PMJake
11/21/2022, 5:16 PMwilliamdclt
11/21/2022, 5:18 PMJake
11/21/2022, 5:18 PMwilliamdclt
11/21/2022, 5:18 PMsql
SELECT namespace, object_id, relation, userset_namespace, userset_object_id, userset_relation, created_transaction, deleted_transaction
FROM relation_tuple
WHERE created_xid IS NULL
ORDER BY created_xid
LIMIT 1000;
With this index:
sql
CREATE INDEX ix_backfill_tuple_temp_2 ON public.relation_tuple USING btree (created_xid) WHERE (created_xid IS NULL);
Jake
11/21/2022, 5:20 PMdesc
thing without your new index?williamdclt
11/21/2022, 5:21 PMsql
Limit (cost=504061.89..504178.57 rows=1000 width=153) (actual time=1998.702..2003.173 rows=1000 loops=1)
-> Gather Merge (cost=504061.89..1184130.67 rows=5828754 width=153) (actual time=1998.700..2003.088 rows=1000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=503061.87..510347.81 rows=2914377 width=153) (actual time=1989.904..1989.942 rows=568 loops=3)
Sort Key: created_xid DESC
Sort Method: top-N heapsort Memory: 314kB
Worker 0: Sort Method: top-N heapsort Memory: 296kB
Worker 1: Sort Method: top-N heapsort Memory: 310kB
-> Parallel Seq Scan on relation_tuple (cost=0.00..343269.72 rows=2914377 width=153) (actual time=268.602..1185.586 rows=2331861 loops=3)
Filter: (created_xid IS NULL)
Rows Removed by Filter: 1411000
Planning Time: 0.806 ms
Execution Time: 2003.370 ms
Jake
11/21/2022, 5:21 PMwilliamdclt
11/21/2022, 5:21 PMsql
EXPLAIN ANALYZE
SELECT namespace, object_id, relation, userset_namespace, userset_object_id, userset_relation, created_transaction, deleted_transaction
FROM relation_tuple
WHERE created_xid IS NULL
ORDER BY created_xid DESC
LIMIT 1000;
Jake
11/21/2022, 5:23 PMwilliamdclt
11/21/2022, 5:24 PM.2
actually but yeaJake
11/21/2022, 5:24 PMwilliamdclt
11/21/2022, 5:25 PMset enable_seqscan = off
😄Jake
11/21/2022, 5:27 PMwilliamdclt
11/21/2022, 5:27 PMJake
11/21/2022, 5:28 PMset enable_seqscan = off
williamdclt
11/21/2022, 5:29 PMon
in real life thoughoff
in tests won't be representativeJake
11/21/2022, 5:29 PMwilliamdclt
11/21/2022, 5:30 PMSET log_statement TO 'all';
and grep through logs for a seq scan?Jake
11/21/2022, 6:16 PMwilliamdclt
11/21/2022, 6:27 PMsql
CREATE INDEX CONCURRENTLY ix_backfill_tuple_temp_2 ON public.relation_tuple USING btree (namespace, object_id, relation, userset_namespace, userset_object_id, userset_relation, created_transaction, deleted_transaction);
Jake
11/21/2022, 6:30 PMSeq Scan on relation_tuple relation_tuple_1 (cost=0.00..416979.27 rows=5246140 width=151) (actual time=732.178..733.167 rows=2000 loops=1)
williamdclt
11/21/2022, 6:31 PMJake
11/21/2022, 6:32 PMcreated_xid, created_id
williamdclt
11/21/2022, 6:35 PMcreated_id
? Is that created_transaction
?Jake
11/21/2022, 6:35 PMwilliamdclt
11/21/2022, 6:36 PMmichael93
12/07/2022, 4:42 PMUPDATE relation_tuple
SET deleted_xid = deleted_transaction::text::xid8,
created_xid = created_transaction::text::xid8
WHERE (id) IN (SELECT id
FROM relation_tuple
WHERE created_xid IS NULL
LIMIT 10000 FOR UPDATE);
any reason why the select is performed on the individual columns? From what I read in the Postgres docs (https://www.postgresql.org/docs/14/sql-select.html#SQL-FOR-UPDATE-SHARE), the lock is on the whole row (see also https://www.postgresql.org/docs/14/explicit-locking.html#LOCKING-ROWS), regardless of the selected fields
Am I missing something along the way? 🙂
Thank you very muchJake
12/07/2022, 7:00 PMmichael93
12/08/2022, 8:10 AM