主页 / user-181111

Anthony's questions

Martin Hope
Anthony
Asked: 2019-11-06 07:18:01 +0800 CST
  • 0

我有 15M 记录的表 A 和 5k 记录的表 B。我需要对两者执行内部连接，但查询时间相当长。

explain analyze 
SELECT distinct(a.student_id), b.student_name, a.class_year
FROM table_a a
INNER JOIN table_b b on a.student_id = b.student_id;

解释计划

                                                                          QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=3628096.85..3779293.37 rows=11780855 width=50) (actual time=35421.004..50690.702 rows=5078 loops=1)
   ->  Sort  (cost=3628096.85..3665895.98 rows=15119652 width=50) (actual time=35421.002..46385.451 rows=14264755 loops=1)
         Sort Key: a.student_id, b.student_name, a.class_year
         Sort Method: external merge  Disk: 890528kB
         ->  Hash Join  (cost=242.20..1308298.78 rows=15119652 width=50) (actual time=3.877..22332.795 rows=14264755 loops=1)
               Hash Cond: ((a.student_id)::text = (b.student_id)::text)
               ->  Seq Scan on table_a a  (cost=0.00..1268336.52 rows=15119652 width=25) (actual time=0.035..6168.042 rows=15119652 loops=1)
               ->  Hash  (cost=174.31..174.31 rows=5431 width=45) (actual time=3.822..3.822 rows=5431 loops=1)
                     Buckets: 8192  Batches: 1  Memory Usage: 483kB
                     ->  Seq Scan on table_b b  (cost=0.00..174.31 rows=5431 width=45) (actual time=0.008..1.886 rows=5431 loops=1)
 Planning time: 2.386 ms
 Execution time: 50822.593 ms
(12 rows)

我有一个table_a索引student_id

"student_id" btree (student_id)
postgresql index
Martin Hope
Anthony
Asked: 2019-08-23 17:34:59 +0800 CST
  • 1

我有一个名为的表my_table，其中包含大约 30M 条记录。该表在 上设置了索引column_a。但是，我注意到当返回的查询数量很高时，不使用索引。

未使用的索引：

explain (buffers, analyze) select * from my_table where column_a in (1);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Seq Scan on my_table  (cost=0.00..1306095.85 rows=7772384 width=648) (actual time=0.009..6231.312 rows=7720995 loops=1)
   Filter: (column_a = 1)
   Rows Removed by Filter: 7398657
   Buffers: shared hit=463612 read=653528
 Planning time: 0.840 ms
 Execution time: 7717.923 ms
(6 rows)

使用的索引：

explain (buffers, analyze) select * from my_table where column_a in (8);
                                                                            QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on my_table  (cost=1378.58..233236.51 rows=73567 width=648) (actual time=14.258..67.795 rows=74756 loops=1)
   Recheck Cond: (column_a = 8)
   Heap Blocks: exact=36425
   Buffers: shared hit=36632
   ->  Bitmap Index Scan on my_table_column_a_idx  (cost=0.00..1360.19 rows=73567 width=0) (actual time=8.595..8.596 rows=74756 loops=1)
         Index Cond: (column_a = 8)
         Buffers: shared hit=207
 Planning time: 0.855 ms
 Execution time: 82.253 ms
(9 rows)

我把桌子吸尘了：

select relallvisible, relpages, relallvisible/relpages as ratio from pg_class where relname='my_table';
 relallvisible | relpages | ratio
---------------+----------+-------
       1117140 |  1117140 |     1
(1 row)
postgresql optimization
Martin Hope
Anthony
Asked: 2019-05-25 06:32:33 +0800 CST
  • 1

我有一个包含some_column数据类型列的表integer。
我注意到我的 PostgreSQL 10.6 实例足够聪明，可以将以下两个查询解释为相同：

select * from my_table where some_column = 5; -- no quotes

select * from my_table where some_column = '5'; -- quotes added

where在列的子句中添加引号会产生什么影响integer？
它会对包含 2000 万行的大表产生性能影响吗？
（有一个索引some_column。）

postgresql performance
Martin Hope
Anthony
Asked: 2019-05-21 18:17:24 +0800 CST
  • 0

我正在通过以下查询运行，EXPLAIN ANALYZE但运行需要很长时间。根据 的输出EXPLAIN ANALYZE，我可以对我的表执行哪些操作来加快此查询的执行速度？

select count(amount), sum(amount) 
from mytable 
where color_code = 5 and shade_type = 'Light';

EXPLAIN ANALYZE输出：

 Finalize Aggregate  (cost=946640.31..946640.32 rows=1 width=40) (actual time=4799.256..4799.257 rows=1 loops=1)
   ->  Gather  (cost=946640.09..946640.30 rows=2 width=40) (actual time=4799.191..4800.566 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=945640.09..945640.10 rows=1 width=40) (actual time=4797.002..4797.003 rows=1 loops=3)
               ->  Parallel Seq Scan on mytable  (cost=0.00..945573.39 rows=13338 width=6) (actual time=656.722..4791.404 rows=10103 loops=3)
                     Filter: ((color_code = 5) AND ((shade_type)::text = 'Light'::text))
                     Rows Removed by Filter: 4888180
 Planning time: 0.257 ms
 Execution time: 4800.661 ms
(10 rows)
postgresql explain