Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL

út 21. 12. 2021 v 19:58 odesílatel Michael Lewis <mlewis(at)entrata(dot)com>
napsal:

> On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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)