explain analyze verbose select * from t2,t3 where t2.n1=t3.n2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=85.58..257.60 rows=6950 width=82) (actual time=0.029..0.031 rows=1 loops=1)
Output: t2.n1, t3.n2
Merge Cond: (t2.n1 = (t3.n2)::bpchar)
-> Index Only Scan using idx_t2 on public.t2 (cost=0.15..65.10 rows=1130 width=44) (actual time=0.013..0.014 rows=1 loops=1)
Output: t2.n1
Heap Fetches: 1
-> Sort (cost=85.43..88.50 rows=1230 width=38) (actual time=0.011..0.011 rows=2 loops=1)
Output: t3.n2
Sort Key: t3.n2 USING <
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.t3 (cost=0.00..22.30 rows=1230 width=38) (actual time=0.004..0.005 rows=2 loops=1)
Output: t3.n2
Planning Time: 0.078 ms
Execution Time: 0.049 ms
(14 rows)
analyze 实际执行一次语句
verbose 是否显示详细信息
收集统计信息
analyze t2;
\x
postgres=# select * from pg_stats where tablename='t2';
-[ RECORD 1 ]----------+-------
schemaname | public
tablename | t2
attname | n1
inherited | f
null_frac | 0
avg_width | 11
n_distinct | -1
most_common_vals |
most_common_freqs |
histogram_bounds |
correlation |
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
select * from pg_stat_user_tables;