目前使用 Postgres 14。
运行CLUSTER sometable USING someindex;
很棒,但是是否有一个查询可以确定表在多次更新和删除后变得如何“非集群”?
谷歌搜索并阅读https://www.postgresql.org/docs/14/sql-cluster.html只是说“设置定期维护脚本......以便定期重新集群所需的表”,这是没有帮助的,因为它没有没有给出任何关于如何确定定期重新聚类的周期的指示。
目前使用 Postgres 14。
运行CLUSTER sometable USING someindex;
很棒,但是是否有一个查询可以确定表在多次更新和删除后变得如何“非集群”?
谷歌搜索并阅读https://www.postgresql.org/docs/14/sql-cluster.html只是说“设置定期维护脚本......以便定期重新集群所需的表”,这是没有帮助的,因为它没有没有给出任何关于如何确定定期重新聚类的周期的指示。
pg_log_backend_memory_contexts
(PG14.1) 下面是PID的输出,其中pg_proctab()
表示。我添加了一些点,以便更轻松地读取大量数字,并且匿名了一些内容。rss
216MB
我不明白:
logging memory contexts of PID 15744
level: 0; TopMemoryContext: 4.264.856 total in 104 blocks; 2.074.448 free (8510 chunks); 2190408 used
level: 1; pgstat TabStatusArray lookup hash table: 65536 total in 4 blocks; 4672 free (11 chunks); 60864 used
level: 1; RdsSuperUserCache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 3792 free (1 chunks); 4400 used: get_date_to_char(timestamp without time zone,character varying)
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 7032 free (5 chunks); 9352 used: get_number_to_char(numeric,character varying)
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 3824 free (3 chunks); 4368 used: get_to_number(character varying,character varying)
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 4504 free (1 chunks); 3688 used: <FUNCTION>
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 6616 free (4 chunks); 9768 used: <FUNCTION2>
level: 1; Sequence values: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; Btree proof lookup cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 5752 free (5 chunks); 10632 used: get_date_interval(character varying,numeric)
level: 1; PL/pgSQL function: 32768 total in 3 blocks; 16608 free (5 chunks); 16160 used: <FUNCTION3>
level: 1; Operator lookup cache: 24576 total in 2 blocks; 10752 free (3 chunks); 13824 used
level: 1; RI compare cache: 16384 total in 2 blocks; 6656 free (3 chunks); 9728 used
level: 1; RI query cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; RI constraint cache: 40648 total in 2 blocks; 2616 free (0 chunks); 38032 used
level: 1; PL/pgSQL function: 8192 total in 1 blocks; 4592 free (1 chunks); 3600 used: get_systimestamp()
level: 1; Type information cache: 24376 total in 2 blocks; 2616 free (0 chunks); 21760 used
level: 1; PLpgSQL cast cache: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; PLpgSQL cast expressions: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; PL/pgSQL function: 16384 total in 2 blocks; 5992 free (4 chunks); 10392 used: <FUNCTION4>
level: 1; Function stat entries: 16384 total in 2 blocks; 6640 free (2 chunks); 9744 used
level: 1; CFuncHash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; Rendezvous variable hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; PLpgSQL function hash: 24528 total in 2 blocks; 2616 free (0 chunks); 21912 used
level: 1; Prepared Queries: 16384 total in 2 blocks; 6656 free (3 chunks); 9728 used
level: 1; TableSpace cache: 8192 total in 1 blocks; 2088 free (0 chunks); 6104 used
level: 1; RowDescriptionContext: 57400 total in 3 blocks; 24272 free (7 chunks); 33128 used
level: 1; MessageContext: 8192 total in 1 blocks; 6888 free (1 chunks); 1304 used
level: 1; Operator class cache: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; smgr relation table: 2.097.152 total in 9 blocks; 269224 free (33 chunks); 1827928 used
level: 1; TransactionAbortContext: 32768 total in 1 blocks; 32504 free (0 chunks); 264 used
level: 1; Portal hash: 8192 total in 1 blocks; 552 free (0 chunks); 7640 used
level: 1; TopPortalContext: 8192 total in 1 blocks; 7928 free (2 chunks); 264 used
level: 1; Relcache by OID: 1.048.576 total in 8 blocks; 500672 free (16 chunks); 547904 used
level: 1; CacheMemoryContext: 69.478.096 total in 208 blocks; 44.098.912 free (106056 chunks); 25.379.184 used
level: 2; CachedPlanSource: 4096 total in 3 blocks; 1960 free (1 chunks); 2136 used: <INSERT>
level: 3; unnamed prepared statement: 16384 total in 2 blocks; 6824 free (1 chunks); 9560 used
level: 2; CachedPlan: 8.420.408 total in 15 blocks; 1409824 free (1 chunks); 7010584 used: <SELECT>
level: 2; index info: 2048 total in 2 blocks; 528 free (1 chunks); 1520 used: pg_toast_295195_index
level: 2; CachedPlan: 8192 total in 4 blocks; 2480 free (0 chunks); 5712 used: <DELETE>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: pg_sequence_seqrelid_index
level: 2; CachedPlan: 2048 total in 2 blocks; 280 free (0 chunks); 1768 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 16 free (0 chunks); 2032 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 160 free (1 chunks); 1888 used: <OTHER UNIQUE SQL>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: <OTHER UNIQUE SQL>
level: 2; index info: 3072 total in 2 blocks; 696 free (1 chunks); 2376 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; CachedExpression: 1024 total in 1 blocks; 488 free (0 chunks); 536 used
level: 2; CachedPlan: 2048 total in 2 blocks; 208 free (1 chunks); 1840 used: TO_NUMBER(p_input, p_fmt)
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 856 free (1 chunks); 2216 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 920 free (0 chunks); 1128 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3544 total in 3 blocks; 960 free (1 chunks); 2584 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4096 total in 3 blocks; 1912 free (3 chunks); 2184 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 840 free (0 chunks); 1208 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 504 free (0 chunks); 1544 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 856 free (1 chunks); 2216 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4024 total in 3 blocks; 816 free (1 chunks); 3208 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 7032 total in 4 blocks; 1928 free (2 chunks); 5104 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 4744 total in 3 blocks; 240 free (0 chunks); 4504 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 7032 total in 4 blocks; 1928 free (2 chunks); 5104 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3304 total in 3 blocks; 784 free (1 chunks); 2520 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3304 total in 3 blocks; 784 free (1 chunks); 2520 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 904 free (0 chunks); 1144 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 824 free (0 chunks); 1224 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 448 free (1 chunks); 1600 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 8192 total in 4 blocks; 3824 free (2 chunks); 4368 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 544 free (2 chunks); 1504 used: <OTHER UNIQUE INDEX NAME>
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: v_interval::INTERVAL
level: 2; CachedPlan: 2048 total in 2 blocks; 528 free (0 chunks); 1520 used: (v_interval IS NOT NULL)
level: 2; CachedPlan: 2048 total in 2 blocks; 144 free (1 chunks); 1904 used: v_interval := p_interval_length || ' second'
level: 2; CachedPlan: 2048 total in 2 blocks; 224 free (1 chunks); 1824 used: (p_interval = 'SECOND')
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1096 free (2 chunks); 1976 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1160 free (1 chunks); 1912 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 688 free (1 chunks); 1360 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1080 free (1 chunks); 1992 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 3072 total in 2 blocks; 1016 free (1 chunks); 2056 used: <OTHER UNIQUE INDEX NAME>
level: 2; index info: 2048 total in 2 blocks; 608 free (1 chunks); 1440 used: <OTHER UNIQUE INDEX NAME>
level: 1; 4141 more child contexts containing 12.748.648 total in 8713 blocks; 3.899.520 free (3559 chunks); 8.849.128 used
level: 1; WAL record construction: 49768 total in 2 blocks; 6360 free (0 chunks); 43408 used
level: 1; PrivateRefCount: 8192 total in 1 blocks; 1576 free (0 chunks); 6616 used
level: 1; MdSmgr: 524.288 total in 7 blocks; 412.640 free (8661 chunks); 111648 used
level: 1; LOCALLOCK hash: 1.048.576 total in 8 blocks; 215936 free (27 chunks); 832640 used
level: 1; Timezones: 104120 total in 2 blocks; 2616 free (0 chunks); 101504 used
level: 1; ErrorContext: 8192 total in 1 blocks; 7928 free (4 chunks); 264 used
Grand total: 100.635.144 bytes in 9342 blocks; 53.175.720 free (127010 chunks); 47459424 used
我有下面的 SQL 查询,运行速度非常慢。至于这个查询,这是由于“ORDER BY”语句造成的,因为Postgres通过“计数器”扫描changes
表,它可以有数百万个值。删除“ORDER BY”语句使查询速度更快。
对于上面提到的其他查询,我通过在两个字段上创建索引来优化它。然而,对于这个查询,我不确定哪个索引是正确的。我尝试打开索引,(item_id, counter)
但它根本没有帮助,我不知道我还能尝试什么。有什么建议么?
慢SQL查询:
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND type = 2
AND item_id IN (SELECT item_id FROM user_items WHERE user_id = 'xxxx')
ORDER BY "counter" ASC
LIMIT 200;
解释(分析、缓冲区、设置)结果:
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1001.15..27628.99 rows=200 width=99) (actual time=98730.912..116273.818 rows=200 loops=1)
Buffers: shared hit=78113369 read=3224064 dirtied=3
I/O Timings: read=137436.119
-> Gather Merge (cost=1001.15..10431526.45 rows=78343 width=99) (actual time=98730.911..116273.783 rows=200 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=78113369 read=3224064 dirtied=3
I/O Timings: read=137436.119
-> Nested Loop (cost=1.13..10421483.70 rows=32643 width=99) (actual time=98493.185..112919.559 rows=75 loops=3)
Buffers: shared hit=78113369 read=3224064 dirtied=3
I/O Timings: read=137436.119
-> Parallel Index Scan using changes_pkey on changes (cost=0.56..5949383.56 rows=6197986 width=99) (actual time=1.076..42523.117 rows=4075591 loops=3)
Index Cond: (counter > '-1'::integer)
Filter: (type = 2)
Rows Removed by Filter: 10370914
Buffers: shared hit=18993521 read=2672415
I/O Timings: read=85551.814
-> Index Scan using user_items_item_id_index on user_items (cost=0.56..0.72 rows=1 width=23) (actual time=0.017..0.017 rows=0 loops=12226772)
Index Cond: ((item_id)::text = (changes.item_id)::text)
Filter: ((user_id)::text = 'xxxx'::text)
Rows Removed by Filter: 1
Buffers: shared hit=59119848 read=551649 dirtied=3
I/O Timings: read=51884.305
Settings: effective_cache_size = '16179496kB', jit = 'off', work_mem = '100000kB'
Planning Time: 1.465 ms
Execution Time: 116273.929 ms
(26 rows)
索引:
"changes_pkey" PRIMARY KEY, btree (counter)
"changes_id_index" btree (id)
"changes_id_unique" UNIQUE CONSTRAINT, btree (id)
"changes_item_id_index" btree (item_id)
"changes_user_id_counter_index" btree (user_id, counter)
"changes_user_id_index" btree (user_id)
我有一个昂贵的函数,它仅在满足某些条件时提供相关数据。该数据通过 LEFT JOIN LATERAL 插入到结果中,如下所示。现在,该函数会为每一行调用。
有没有办法重写它,以便仅对 t1.type 等于 5 的行调用它?尽管我设置了条件,但下面仍然为每一行调用该函数。
SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5;
测试用例:
CREATE TABLE table1 (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
type INTEGER,
col1 INTEGER,
col2 INTEGER
);
-- Sample data for table1
INSERT INTO table1 (name, type, col1, col2)
VALUES
('Row 1', 5, 10, 20),
('Row 2', 7, 15, 25),
('Row 3', 10, 30, 40);
CREATE OR REPLACE FUNCTION function_name(col1_arg INTEGER, col2_arg INTEGER)
RETURNS TABLE (col1 INTEGER, col2 INTEGER)
AS $$
BEGIN
RAISE LOG 'Function called';
RETURN QUERY SELECT col1_arg * 2 AS col1, col2_arg * 3 AS col2;
END;
$$ LANGUAGE plpgsql;
-- Test Query
SELECT t1.name, t1.type, t2.col1, t2.col2
FROM table1 t1
LEFT JOIN LATERAL function_name(t1.col1, t1.col2) AS t2 ON t1.type = 5;
我一直在尝试调试一个永远不会完成的特别慢的查询(它需要永远并最终超时),并发现它取决于语句ORDER BY
:如果它在那里,它永远不会完成,如果我删除它,它会立即返回。
我的假设是该字段没有索引,但是我发现有一个:
CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
但这似乎没有任何区别,所以我想知道原因是什么?这可能是因为它是一个“唯一索引”,与该表上的其他索引不同?
请参阅下面的查询:
永远不会完成:
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
ORDER BY "counter" ASC
LIMIT 200
立即完成:
SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
FROM "changes"
WHERE counter > -1
AND (type = 1 OR type = 3)
AND user_id = 'xxxxxxx'
LIMIT 200
该表上的索引:
changes | changes_id_index | CREATE INDEX changes_id_index ON public.changes USING btree (id)
changes | changes_id_unique | CREATE UNIQUE INDEX changes_id_unique ON public.changes USING btree (id)
changes | changes_item_id_index | CREATE INDEX changes_item_id_index ON public.changes USING btree (item_id)
changes | changes_pkey | CREATE UNIQUE INDEX changes_pkey ON public.changes USING btree (counter)
changes | changes_user_id_index | CREATE INDEX changes_user_id_index ON public.changes USING btree (user_id)
postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> ORDER BY "counter" ASC
postgres-> LIMIT 200;
QUERY PLAN
-----------------------------------------------------------------------------------------------------
Limit (cost=0.56..9206.44 rows=200 width=99)
-> Index Scan using changes_pkey on changes (cost=0.56..5746031.01 rows=124834 width=99)
Index Cond: (counter > '-1'::integer)
Filter: (((user_id)::text = 'xxxxxxxx'::text) AND ((type = 1) OR (type = 3)))
(4 rows)
*解释一下快速查询:
postgres=> EXPLAIN SELECT "id", "item_id", "item_name", "type", "updated_time", "counter"
postgres-> FROM "changes"
postgres-> WHERE counter > -1
postgres-> AND (type = 1 OR type = 3)
postgres-> AND user_id = 'xxxxxxxx'
postgres-> LIMIT 200;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Limit (cost=0.56..1190.09 rows=200 width=99)
-> Index Scan using changes_user_id_index on changes (cost=0.56..742468.10 rows=124834 width=99)
Index Cond: ((user_id)::text = 'xxxxxxxx'::text)
Filter: ((counter > '-1'::integer) AND ((type = 1) OR (type = 3)))
(4 rows)
知道查询速度慢的原因是什么吗?
在机器故障和匆忙将数据传输到另一台机器上之后,我们以某种方式结束了一些全新的序列替换旧序列作为身份列(以及一些旧序列)的支持。
我们可以使用RESET
表中的最大值来创建新序列,并且还可以将ALTER SEQUENCE ... RENAME
新序列重命名为旧名称(以绕过客户端代码中的任何硬编码),但我想知道是否可以交换旧序列顺序回到原位?
我正在 Yellowbrick DB 中编写 Pl/pgSQL 连接查询。
我一次加入 6 张桌子。一切正常。
突然我发现我的查询抛出错误:“IllegalArgumentException”
检查了Yellowbrick错误代码👉链接
但没有找到太多信息:
您对为什么“IllegalArgumentException”在 Yellowbrick DB 中的 PL/pgSQL 查询中抛出有什么建议吗?
如果您对此有任何想法,请分享一些内容。
注意:我没有找到 Yellowbrick 标签,所以提及 pl/pgsql 标签
我想知道是否可以添加某种检查,以便在更新部分成功时中止数据修改 CTE。附件是代表问题的最小查询。如果Updated_rows尚未更新 WHERE IN 子句中指定的 3 行,我希望查询失败。
如果将此查询重写为交互式事务,则很容易实现这一点,但我想知道是否可以在将事物保留为单个数据修改 CTE 的同时实现此目的。
with updated_rows as (
update some_table
set status = 'SUCCEEDED'
where id in ('a', 'b', 'c') and status = 'PENDING'
returning *
), more_updated_rows as (
update another_table
set total = total + 3
where completed = false
returning *
) select * from more_updated_rows
我正在尝试使用 Postgresql 15.5 数据库设置一个新项目。
我有一个正在运行的实例。如果我创建一个新数据库
psql --command "create database \"tagger-db\""
创建一个新用户
psql --command "create user \"tagger-user\""
将新数据库的所有权限授予新用户
psql --command "grant all privileges on database \"tagger-db\" to \"tagger-user\""
如果我然后尝试使用新用户创建一个表
psql -U "tagger-user" -d "tagger-db" --command "create table users (id uuid primary key)"
我收到以下错误
ERROR: permission denied for schema public
向新用户授予数据库所有权限的正确方法是什么?
可以在 plpgsql 中捕获异常:
EXCEPTION WHEN unique_violation THEN
但如何检查是否违反特定约束呢?
以下示例创建两个键:一个用于a
,一个用于b
。如何知道是哪一个导致了异常呢?
create table t (
a text unique,
b text unique
);
create procedure insert_t(a text, b text)
language plpgsql
as $$
begin
insert into t values (a, b);
exception when unique_violation then
raise notice 'is it a or b?';
end
$$;
call insert_t('x', 'foo');
call insert_t('x', 'bar');
select * from t;