út 21. 12. 2021 v 19:58 odesílatel Michael Lewis <mle...@entrata.com> napsal:
> On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule <pavel.steh...@gmail.com> > wrote: > >> I wrote about it. Did you read this article? >> >> https://okbob.blogspot.com/2018/02/schema-variables.html >> >> The goals of this project: >> >> - fast non transactional non persistent (session based) storage, >> >> Would there be statistics that are considered in query planning, or would > that be impossible or just out of scope initially? > The session variable has no statistics - but it is used like any other external parameter - like PL/pgSQL variables. postgres=# create variable xx as int; CREATE VARIABLE postgres=# create table xxx(a int); CREATE TABLE postgres=# insert into xxx select 0 from generate_series(1,10000); INSERT 0 10000 postgres=# insert into xxx select 1 from generate_series(1,10); INSERT 0 10 postgres=# analyze xxx; ANALYZE postgres=# create index on xxx(a); CREATE INDEX postgres=# let xx = 1; LET postgres=# explain analyze select * from xxx where a = xx; ┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Index Only Scan using xxx_a_idx on xxx (cost=0.29..8.46 rows=10 width=4) (actual time=0.044..0.048 rows=10 loops=1) │ │ Index Cond: (a = xx) │ │ Heap Fetches: 10 │ │ Planning Time: 0.237 ms │ │ Execution Time: 0.072 ms │ └──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (5 rows) postgres=# let xx = 0; LET postgres=# explain analyze select * from xxx where a = xx; ┌───────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ QUERY PLAN │ ╞═══════════════════════════════════════════════════════════════════════════════════════════════════════╡ │ Seq Scan on xxx (cost=0.00..170.12 rows=10000 width=4) (actual time=0.036..4.373 rows=10000 loops=1) │ │ Filter: (a = xx) │ │ Rows Removed by Filter: 10 │ │ Planning Time: 0.281 ms │ │ Execution Time: 5.711 ms │ └───────────────────────────────────────────────────────────────────────────────────────────────────────┘ (5 rows)
