On Tue, Apr 14, 2020 at 11:25 AM Pavel Stehule <pavel.steh...@gmail.com> wrote: > > út 14. 4. 2020 v 10:40 odesílatel Amit Langote <amitlangot...@gmail.com> > napsal: >> >> On Tue, Apr 14, 2020 at 5:27 PM Julien Rouhaud <rjuju...@gmail.com> wrote: >> > On Tue, Apr 14, 2020 at 10:18 AM Pavel Stehule <pavel.steh...@gmail.com> >> > wrote: >> > > For second run I get >> > > >> > > postgres=# EXPLAIN (BUFFERS, ANALYZE) SELECT * FROM obce WHERE okres_id >> > > = 'CZ0201'; >> > > ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ >> > > │ QUERY PLAN >> > > │ >> > > ╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ >> > > │ Index Scan using obce_okres_id_idx on obce (cost=0.28..14.49 rows=114 >> > > width=41) (actual time=0.044..0.101 rows=114 loops=1) │ >> > > │ Index Cond: ((okres_id)::text = 'CZ0201'::text) >> > > │ >> > > │ Buffers: shared hit=4 >> > > │ >> > > │ Planning Time: 0.159 ms >> > > │ >> > > │ Execution Time: 0.155 ms >> > > │ >> > > └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ >> > > (5 rows) >> > > >> > > Now, there is not any touch in planning time. Does it mean so this all >> > > these data are cached somewhere in session memory? >> > >> > The planning time is definitely shorter the 2nd time. And yes, what >> > you see are all the catcache accesses that are initially performed on >> > a fresh new backend. >> >> By the way, even with all catcaches served from local memory, one may >> still see shared buffers being hit during planning. For example: >> >> explain (buffers, analyze) select * from foo where a = 1; >> QUERY PLAN >> ------------------------------------------------------------------------------------------------------------------- >> Index Only Scan using foo_pkey on foo (cost=0.15..8.17 rows=1 >> width=4) (actual time=0.010..0.011 rows=0 loops=1) >> Index Cond: (a = 1) >> Heap Fetches: 0 >> Buffers: shared hit=2 >> Planning Time: 0.775 ms >> Buffers: shared hit=72 >> Execution Time: 0.086 ms >> (7 rows) >> >> Time: 2.477 ms >> postgres=# explain (buffers, analyze) select * from foo where a = 1; >> QUERY PLAN >> ------------------------------------------------------------------------------------------------------------------- >> Index Only Scan using foo_pkey on foo (cost=0.15..8.17 rows=1 >> width=4) (actual time=0.012..0.012 rows=0 loops=1) >> Index Cond: (a = 1) >> Heap Fetches: 0 >> Buffers: shared hit=2 >> Planning Time: 0.102 ms >> Buffers: shared hit=1 >> Execution Time: 0.047 ms >> (7 rows) >> >> It seems that 1 Buffer hit comes from get_relation_info() doing >> _bt_getrootheight() for that index on foo. > > > unfortunatelly, I cannot to repeat it. > > create table foo(a int); > create index on foo(a); > insert into foo values(1); > analyze foo; > > for this case any second EXPLAIN is without buffer on my comp
_bt_getrootheight() won't cache any value if the index is totally empty. Removing the INSERT in your example should lead to Amit's behavior.
