☰

EXPLAIN (BUFFER,ANALYZE) 理解

tag-icon • tag-icon performance postgresql database
EXPLAIN (BUFFER,ANALYZE) 理解

我正在使用 PostgreSQL 9.3，有人能帮我做些什么来改善这个问题吗？数据库表中的最大记录数约为 5000 到 7000，每天都在变化。PGsql DB 中的 shared_buffers 设置为 =1GB。这里出了什么问题。我的查询 o/p 是
QUERY PLAN

Nested Loop  (cost=0.85..10873.44 rows=75 width=271) (actual time=0.123..51.515 rows=3982 loops=1)
   Buffers: shared hit=18475 read=55682
   Index Scan using idx_chrghist_picked_status on charge_history  (cost=0.42..10239.13 rows=75 width=255) (actual time=0.092..16.022 rows=3982 loops=1)
         Index Cond: (picked_status = 'NOTPICKED'::text)
         Filter: ((updated_date)::date = (('now'::cstring)::date - 1))
         Rows Removed by Filter: 10022
         Buffers: shared hit=2547 read=55682
    Index Scan using "CUSTOMERID" on subscriber_data  (cost=0.43..8.45 rows=1 width=36) (actual time=0.008..0.008 rows=1 loops=3982)
         Index Cond: ((customer_id)::text = (charge_history.customer_id)::text)
         Buffers: shared hit=15928
Total runtime: 52.053 ms

询问 ：

EXPLAIN (BUFFERS,ANALYZE)
SELECT
  subscriber_data.customer_id ,
  charge_history.charge_trx_id,
  charge_history.updated_date,
  charge_history.command_tx_id ,
  charge_history.mvno_id,
  charge_history.customer_id,
  charge_history.extra_plan_id,
  charge_history.base_plan_id,
  charge_history.old_base_plan_id,
  charge_history.volume,
  charge_history.price,
  charge_history.charge_type,
  charge_history.remarks
FROM charge_history , subscriber_data
WHERE subscriber_data.customer_id=charge_history.customer_id
  AND charge_history.updated_date::date=(CURRENT_DATE - integer '1')
  AND 'charge_history.picked_status = 'NOTPICKED';

答案1

此查询运行时间为 52 毫秒，可能不算多。它包含两次索引扫描和一个嵌套循环，返回 3,982 行。

您也许不能使用text，或者设置NOTPICKED为null（如果该列尚未使用null）。

相关内容