Luan Huynh's questions

PostgreSQL v9.6, postgres_fdw

Mesa estrangeira

CREATE FOREIGN TABLE user_info (
  id bigint ,
  info jsonb 
) 
SERVER server_test_fdw OPTIONS(SCHEMA_NAME 'public', TABLE_NAME 'user_info_raw' );
-- user_info_raw is a large table (100 million records, 200 GB)

Dados de amostra da info coluna

{"key1": 1, "key2": 0.678}
{"key1": 1, "key2": 1.0}
{"key1": 1, "key2": 0.986} 
{"key1": 2, "key2": 0.75}
{"key1": 2, "key2": 0.639} 

Consulta na tabela estrangeira ( atualizada )

SELECT id, info 
FROM user_info
WHERE info ->> 'key1'= '1' -- OR using jsonb_extract_path_text(info, 'key1')  = '1'
ORDER BY id 
LIMIT 10; 

Limit  (cost=10750829.63..10750829.65 rows=10 width=40) (actual time=550059.320..550059.326 rows=10 loops=1)
   ->  Sort  (cost=10750829.63..10751772.77 rows=377257 width=40) (actual time=550059.318..550059.321 rows=10 loops=1)
         Sort Key: id
         Sort Method: top-N heapsort  Memory: 26kB
         ->  Foreign Scan on user_info (cost=100.00..10742677.24 rows=377257 width=40) (actual time=1.413..536718.366 rows=68281020 loops=1)
               Filter: ((info ->> 'key1'::text) = '1'::text)
               Rows Removed by Filter: 7170443
 Planning time: 4.097 ms
 Execution time: 550059.597 ms

Consulta em user_info_raw

EXPLAIN ANALYSE
SELECT id, info 
FROM user_info_raw
WHERE info ->> 'key1'= '1'
ORDER BY id 
LIMIT 10;

 Limit  (cost=0.57..1296.95 rows=10 width=59) (actual time=0.043..0.073 rows=10 loops=1)
   ->  Index Scan using idx_user_info_raw_info on user_info_raw  (cost=0.57..68882850.88 rows=531346 width=59) (actual time=0.042..0.070 rows=10 loops=1)
         Filter: ((info ->> 'key1'::text) = '1'::text)
 Planning time: 0.192 ms
 Execution time: 0.102 ms 

select pg_size_pretty(pg_table_size('user_info_raw'));
 pg_size_pretty 
----------------
 223 GB

Demora 10 milissegundos para executar a consulta no user_info_raw ( servidor remoto ).

Mas, leva muito tempo ao usar a user_infotabela estrangeira. Quando eu removo ORDER BY id, a consulta é executada muito rápido .

Eu acho que minha consulta na tabela estrangeira deve enviar para o servidor remoto para execução, mas não é, não sei porque, pode ser devido a esse trecho do documento postgres_fdw

Por padrão, apenas as cláusulas WHERE que usam operadores e funções integrados serão consideradas para execução no servidor remoto. As cláusulas que envolvem funções não internas são verificadas localmente depois que as linhas são buscadas. Se tais funções estiverem disponíveis no servidor remoto e puderem ser confiáveis ​​para produzir os mesmos resultados que produzem localmente, o desempenho pode ser melhorado enviando tais cláusulas WHERE para execução remota

Como posso corrigir o problema relacionado ao ORDER BY na tabela estrangeira?

ATUALIZADA

Adicionando use_remote_estimatee servernão foreign tableajuda.

Estou usando o PostgreSQL 9.6 no Linux. Recebi um erro ao fazer um teste na função chr() .

postgres=# select chr(1199111);
ERROR:  requested character too large for encoding: 1199111
postgres=# select chr(55296);
ERROR:  requested character not valid for encoding: 55296
postgres=# select chr(100000);
 chr
-----
 ?
(1 row)

Aqui, como você pode ver com o valor 100k, funcionou ( não gerou erro ), porém outros não. Estou curioso sobre isso. Alguém poderia por favor me explicar o porquê?

Eu anexo script de teste

do
$$
declare 
str text ;
begin
for i in 1..1200000
loop
begin
select chr(i) into str;
exception when others then raise notice '=> i: %     => str: %', i , str ;
#exit; -- you can uncomment this star key
end ;
end loop;
end 
$$ ;

ATUALIZADA

postgres=# show server_encoding;
 server_encoding
-----------------
 UTF8
(1 row)

Descrição

PostgreSQL 9.6 no Linux, tamanho da tags_tmptabela ~ 30 GB (10 milhões de linhas), tagsé um text[]e tem apenas 6 valores.

tags_tmp(id int, tags text[], maker_date timestamp, value text)

id  tags        maker_date      value
1   {a,b,c}     2016-11-09      This is test 
2   {a}         2016-11-08      This is test 
3   {b,c}       2016-11-07      This is test 
4   {c}         2016-11-06      This is test 
5   {d}         2016-11-05      This is test 

Eu preciso recuperar dados com filtro ativado tagse também order byem maker_date desc. Posso criar um índice em ambas as tags & maker_date desccolunas?

Se não, você poderia sugerir outras ideias?

Exemplo de consulta

select id, tags, maker_date, value
from tags_tmp
where  tags && array['a','b']
order by maker_date desc
limit 5 offset 0

Código SQL:

create index idx1 on tags_tmp using gin (tags);
create index idx2 on tags_tmp using btree(maker_date desc);

explain (analyse on, costs on, verbose)
select id, tags, maker_date, value
from tags_tmp
where tags && array['funny','inspiration']
order by maker_date desc
limit 5 offset 0 ;

Explique o resultado:

Limit  (cost=233469.63..233469.65 rows=5 width=116) (actual time=801.482..801.483 rows=5 loops=1)
  Output: id, tags, maker_date, value
  ->  Sort  (cost=233469.63..234714.22 rows=497833 width=116) (actual time=801.481..801.481 rows=5 loops=1)
        Output: id, tags, maker_date, value
        Sort Key: tags_tmp.maker_date DESC
        Sort Method: top-N heapsort  Memory: 25kB
        ->  Bitmap Heap Scan on public.tags_tmp  (cost=6486.58..225200.81 rows=497833 width=116) (actual time=212.982..696.650 rows=366392 loops=1)
              Output: id, tags, maker_date, value
              Recheck Cond: (tags_tmp.tags && '{funny,inspiration}'::text[])
              Heap Blocks: exact=120034
              ->  Bitmap Index Scan on idx1  (cost=0.00..6362.12 rows=497882 width=0) (actual time=171.742..171.742 rows=722612 loops=1)
                    Index Cond: (tags_tmp.tags && '{funny,inspiration}'::text[])
Planning time: 0.185 ms
Execution time: 802.128 ms

Mais Informações

Testei usando índice parcial para apenas uma tag, claro, é mais rápido. Mas eu tenho muitos tag , por exemplo: create index idx_tmp on tags_tmp using btree (maker_date desc) where (tags && array['tag1') or tags && array['tag2'] or ... or tags && array['tag6']. E eu testei entre tags && array['tag1']e 'tag1' = any(tags), o desempenho é o mesmo.

1. text[]tem apenas 6 valores = a, b, c, d, e, f. Por exemplo: tags={a,b,c}, tags={a}, tags={a,c}, tags={a,b,c,d,e,f}, tags={b,f}e assim por diante. Mas não pode ter valor g->z, A-Ze etc.

2. create table tags_tmp(id int primary key not null, tags text[] not null, maker_date timestamp not null, value text)

3. Em termos de distinctvalores de matriz, o tagsque contém aleva 20% das linhas da tabela where 'a' = any(tags), b=20% where 'b' = any(tags), c=20% where 'c' = any(tags), d=20% where 'd' = any(tags), e=10% where 'e' = any(tags), f=10% where 'f' = any(tags).

4. Além disso, (tags, maker_date)não é único.

5. Esta tabela não é somente leitura.

6. É sort on timestamp, mas meu exemplo mostra datas, desculpe por isso.

Situação atual: tags = 'a' or tags = 'b' or tags = 'c'e mais

(1) Com GIN indexou converter text[] to int[], bem como converter text[] to inte mais, ele usará o índice de bitmap em várias tags. Finalmente, após o teste, decidi usar a solução antiga, mudar ORpara muitas UNIONcláusulas, cada UNIONuma limitando o número de dados. Claro, vou criar partial indexpara cada valor de tag, assim como posso combinar com (1) acima. Em termos de OFFSET, ele usará uma ou mais condições na WHEREcláusula.

Exemplo

EXPLAIN (ANALYSE ON, costs ON, VERBOSE)
SELECT rs.*
FROM (
        (SELECT tags,
                id,
                maker_date
         FROM tags_tmp
         WHERE 'a' = any(tags)
           AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
         ORDER BY maker_date DESC LIMIT 5)
      UNION
        (SELECT tags,
                id,
                maker_date
         FROM tags_tmp
         WHERE 'b' = any(tags)
           AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
         ORDER BY maker_date DESC LIMIT 5)
      UNION
        (SELECT tags,
                id,
                maker_date
         FROM tags_tmp
         WHERE 'c' = any(tags)
           AND maker_date <= '2016-03-28 05:43:57.779528'::TIMESTAMP
         ORDER BY maker_date DESC LIMIT 5)) rs
ORDER BY rs.maker_date DESC LIMIT 5 ;

Sobre como projetar o esquema em estrela: tenho três colunas de data em uma tabela de fatos fact_table (insert_date, trade_date, close_date ...). E não sei quantas dimensões de data devem ser criadas?

Caso 1: Dim A . Isso significa: uma linha @fact_table tem três FKs para A.

Caso 2: Dim A (para insert_date), Dim B (trade_date), Dim C (close_date). Isso significa: uma linha @fact_table tem um FK para A, um FK para B e um FK para C .

Pergunta : Quantas dimensões de data devem ser criadas?

Descrição:

Eu tento inserir 1 milhão de linhas em uma tabela vazia no MSSQL 2012 Express. Aqui meu roteiro:

-- set statistics time off
drop table t1
create table t1 (id int, a text, b text) 
go

-- #1 - 1,000,000 - 30s -> 45s
with ID(number) as
(
    select 1 as number
    union all
    select number + 1
    from ID
    where number < 1000000 + 1
)
insert into t1
    select number, 'a_' + cast (number as varchar), 'b_' + cast (number/2 as varchar)
    from ID  
    option(maxrecursion 0)


-- #2 - 1 million rows => ~140,000 rows = 120s (have to cancel query)
declare @count int
set @count = 0
while @count < 1000000
begin
    set @count = @count + 1
    insert into t1 
        values(@count, 'a_' + cast (@count as varchar), 'b_' + cast (@count/2 as varchar))
end

-- #3 - ~1,300,000 rows - 18s -> 20s  

with temp as 
(
    SELECT  ROW_NUMBER() OVER(ORDER BY a.object_id) as tcount 
    from sys.all_columns a,  sys.all_columns b
    where a.object_id = b.object_id  
) 
insert into t1
    select tcount, 'a_' + cast (tcount as varchar), 'b_' + cast (tcount/2 as varchar) 
    from temp 
go

declare @count int
set @count = 0
while @count < 3
begin
    with temp as (select max(id) + 1 as max_id from t1)
    insert into t1
        select max_id, 'a_' + cast (max_id as varchar), 'b_' + cast (max_id/2 as varchar) 
        from t1, temp 
    set @count = @count + 1
end

-- #4 -- 1,000,000 = 3s -> 4s (have to drop t1 first)
with a(k) as
(
select 1 as k
union all
select k + 1 from a where k < 99 + 1
) , 
t2 as (
select row_number() over(order by x.k) as k
from a x , a y , a z 
) 
select k as id , 'a_' + cast (k as varchar) as a, 'b_' + cast (k/2 as varchar) as b into t1
from t2

Pergunta:

Depois de pesquisar, encontrei 4 soluções. Existe alguma solução melhor (não usar dados de cópia de arquivos)?

Descrição:

PostgreSQL 9.3

Corda:'ì ằ ú ề'

Resultado desejado: 'i a u e'

Meu código:

select translate ('ì ằ ú ề', 'ìằúề', 'iaue') ; -- it works. Result: i a u e

Pergunta:

Se eu usar dessa forma, tenho que definir uma tradução manual entre 'ìằúề' e 'iaue'. Existe uma solução melhor?

Ref: Documento PG

Descrição:

Temos uma tabela no PostgreSQL 9.3 e migramos (dados & estrutura) para o Oracle 11.0.2.4. Aqui está a nossa tabela:

create table cstb_temp_log 
(
id NUMBER PRIMARY KEY,
log_info data_type(max_length) -- max_length < 10000
)
-- log_info : data is inserted/updated every second (**)
-- data_type: may be we can use "clob" because max_length of varchar2 in Oracle is 4000 (SQL).
-- cstb_temp_log : size = 1 GB, row = 400000

Queremos indexar e pesquisar "texto" na coluna log_info, então tentamos o Oracle Text 11g .

Problemas:

Se usarmos "data_type" é "clob", podemos usar "índice de contexto" e ele deve sincronizar após DML . (não podemos usar desta forma por causa de (**))

Se houver, como podemos indexar e pesquisar na coluna "log_info" (max_length <10000 e os dados são alterados a cada segundo)?

Tenho um pequeno banco de dados PostgreSQL (v9.3) em Centos 6 x64 ( RAM: 8 GB ).

postgresql.conf

max_connections = 512
shared_buffers = 3000MB  
temp_buffers = 8MB          
work_mem = 2MB          
maintenance_work_mem = 128MB         
effective_cache_size = 3000MB

Cerca de 150 conexões, o PostgreSQL ocupa mais de 6 GB de RAM (claro, outros aplicativos usam cerca de 200 MB de RAM), aqui minhas informações:

Mem:  7062.945M total, 6892.410M used,  170.535M free, 6644.000k buffers
Swap:    0.000k total,    0.000k used,    0.000k free, 5378.922M cached

Perguntas:

1. Por que o PG ocupa muita RAM?

2. Como posso reduzir os buffers em cache do PG?

Descrição

Ambiente: Centos 6-x64, Postgres Plus Advanced Server 9.3.
Tenho uma tabela com 4 colunas com dados conforme abaixo:

id    code_id            effective_date                 name
24      12       "2015-09-15 02:57:47.626751+00"      "dtsc_12"
429     215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
430     215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
465     233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
466     233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
468     234      "2015-09-15 02:57:47.626751+00"      "dtsc_234"

E eu quero obter todas as linhas com condição: group by code_ide max(effective_date) < current_timestamp. Então, o resultado que eu quero:

id     code_id             effective_date                name
24       12       "2015-09-15 02:57:47.626751+00"      "dtsc_12"
429      215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
465      233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
468      234      "2015-09-15 02:57:47.626751+00"      "dtsc_234"

minha codificação

create table win_a (
   id int not null primary key,
   code_id int,
   effective_date timestamp with time zone,
   name text
);

insert into win_a
select a,  a/2,  now() + trunc(random()  * 100) * '1 day'::interval, 'dtsc_' || (a/2)::int
from generate_series(1, 500) a
ORDER BY random() ;

create index win_a_indx on win_a using btree ( code_id, effective_date desc);

-- query 1
select a.*
from (
select id, code_id, effective_date, name
     , rank() over (partition by code_id order by effective_date desc, id) as rank
from win_a
where effective_date < current_timestamp
) a
where rank = 1 ;

-- query 2 -- false if in the same code_id -> have more than two same effective_date value
select a.*
from win_a a 
join (
    select code_id, max(effective_date) as max_ef
    from win_a 
    where effective_date < current_timestamp
    group by code_id ) b 
on a.code_id = b.code_id and a.effective_date = b.max_ef;

-- query 3 -- false if in the same code_id -> have more than two same effective_date value
select a.*
from win_a a 
where (code_id, effective_date) in
     (select code_id, max(effective_date) as max_ef
    from win_a 
    where effective_date < current_timestamp
    group by code_id );

Pergunta

1. Posso usar o índice com a função de janela? (com a consulta 1, tento um índice, mas o Postgres ainda usa um seq-scan em vez de um index-scan)

2. Como posso melhorar minha codificação?

Descrição:

Estamos usando o PostgreSQL 9.3 - Centos 6 x64 . Temos uma tabela dtsc_search_data conforme abaixo:

dtsc_search_data 
id ---- c1 ---- c2 ---- c3
1  ----  1 ----  1 ----  1 
2  ----  1 ----  2 ----  2 
3  ----  1 ----  1 ----  3 

Queremos pesquisar nas colunas "c1, c2, c3" na tabela dtsc_search_data com condições: se search_value for encontrado em "c1", então retorne ; se valor_pesquisa não for encontrado em "c1" então encontre em "c2", se valor_pesquisa for encontrado em "c2" então retorne ; caso contrário, retorne (c3).

Exemplo:

search_value = 1 => "c1" = 1 => just search on "c1"
search value = 2 => "c1" != 2, "c2" = 2 => just search on "c2". 
search_value = 3 => "c3"

CÓDIGO (atualização)

create table dtsc_search_data (id int, c1 int, c2 int , c3 int) ;
 insert into dtsc_search_data values(1,1,1,1);
 insert into dtsc_search_data values(2,1,2,2);
 insert into dtsc_search_data values(3,1,1,3);

 -- search_value = 2
 -- find on c1 column first
     select * 
     from dtsc_search_data
     where c1 = 2
 -- if c1 is not found then c2 -- get value from here
     select * 
     from dtsc_search_data
     where c2 = 2   

Pergunta:

Nossa solução: escreva 3 pesquisas SQL em "c1", "c2", "c3" seguidas pelas condições acima. Como podemos fazer isso com o menor desempenho?

Descrição

Temos uma tabela e dados abaixo:

Version: PostgreSQL 9.3
Table name: tree_data (id int, code text, name text, parent_id int) 
Primary key: id 
Foreign key: parent_id (refer to id)

e dados:

insert into tree_data (id, code, name, parent_id) values (1, 'aaa','aaa', null);
insert into tree_data (id, code, name, parent_id) values (2, 'bbb','bbb', 1);
insert into tree_data (id, code, name, parent_id) values (3, 'ccc','ccc', 1); 

 id | code | name | parent_id
 1    aaa    aaa      null
 2    bbb    bbb      1
 3    ccc    ccc      1

Aqui nossa consulta e resultado que queremos, significa: ao excluir id = 1 (linha pai), a tabela definirá automaticamente parent_id = null nas linhas filhas (primeiro nível).

delete from tree_data where id = 1 ;
----> rows after deleting: 
 id | code | name | parent_id
 2    bbb    bbb      null
 3    ccc    ccc      null

Nossas perguntas:

Podemos usar as restrições do postgresql para fazer isso? Se não, como podemos fazer?

Iniciar arquivo de registro:

2015-03-06 01:57:56: pid 2760: LOG:  Setting up socket for 0.0.0.0:9999
2015-03-06 01:57:56: pid 2760: LOG:  Setting up socket for :::9999
2015-03-06 01:57:56: pid 2760: LOG:  pgpool-II successfully started. version 3.4.
0 (tataraboshi)
2015-03-06 01:57:56: pid 2760: LOG:  find_primary_node: checking backend no 0
2015-03-06 01:57:56: pid 2760: LOG:  find_primary_node: primary node id is 0
2015-03-06 01:58:02: pid 2792: ERROR:  unable to read message length
2015-03-06 01:58:02: pid 2792: DETAIL:  message length (8) in slot 1 does not mat
ch with slot 0(12)

Arquivo de log de depuração:

2015-03-06 01:55:07: pid 2680: LOG:  find_primary_node: primary node id is 0
2015-03-06 01:55:42: pid 2712: DEBUG:  I am 2712 accept fd 6
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  Protocol Major: 1234 Minor: 5679 database
:  user: 
2015-03-06 01:55:42: pid 2712: DEBUG:  selecting backend connection
2015-03-06 01:55:42: pid 2712: DETAIL:  SSLRequest from client
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  application_name: psql
2015-03-06 01:55:42: pid 2712: DEBUG:  reading startup packet
2015-03-06 01:55:42: pid 2712: DETAIL:  Protocol Major: 3 Minor: 0 database: post
gres user: enterprisedb
2015-03-06 01:55:42: pid 2712: DEBUG:  creating new connection to backend
2015-03-06 01:55:42: pid 2712: DETAIL:  connecting 0 backend
2015-03-06 01:55:42: pid 2712: DEBUG:  creating new connection to backend
2015-03-06 01:55:42: pid 2712: DETAIL:  connecting 1 backend
2015-03-06 01:55:42: pid 2712: DEBUG:  reading message length
2015-03-06 01:55:42: pid 2712: DETAIL:  slot: 0 length: 12
2015-03-06 01:55:42: pid 2712: DEBUG:  reading message length
2015-03-06 01:55:42: pid 2712: DETAIL:  slot: 1 length: 8
2015-03-06 01:55:42: pid 2712: ERROR:  unable to read message length
2015-03-06 01:55:42: pid 2712: DETAIL:  message length (8) in slot 1 does not mat
ch with slot 0(12)

comando psql:

[enterprisedb@testing_ppas93]$ psql -h localhost -p 9999 -d postgres
psql: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request

Eu uso o pgpool II para controlar o evento de failover entre dois servidores (mestre no google cloud, escravo no meu local, centos x64, banco de dados: Postgres Plus Advanced Server 9.3). Quando tento conectar meu banco de dados via porta 9999 (exemplo: psql -h localhost -p 9999 -d postgres), o pgpool gera o erro

ERRO: Não foi possível ler o tamanho da mensagem

Tenho uma tabela com 100k linhas criada no PostgreSQL 9.3

create table demo_bbb
(
  id numeric NOT NULL,
  code_bbb character varying,  
  column_02 character varying,  
  column_03 character varying,  
  column_04 character varying,  
  column_05 character varying,  
  column_06 character varying,  
  column_07 character varying,  
  column_08 character varying,  
  column_09 character varying,  
  column_10 character varying,  
  CONSTRAINT demo_bbb_pk PRIMARY KEY (id)
);

Aqui está o meu teste (cada consulta é executada 3 vezes):

(1) select id from demo_bbb b ;         -- database query time: ~26ms
(2) select column_10 from demo_bbb b ;  -- database query time: 46ms, 48ms, 51ms
(3) select column_05 from demo_bbb b ;  -- database query time: 37ms, 38 ms, 45ms
(4) select * from demo_bbb b ;          -- database query time: 28ms, 32ms, 37ms

Resultado: Tempo médio de (4) < tempo de (2), (3) ( 32ms < 45ms )

Explique analise:

(1) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=6) (actual time=0.008..22.088 rows=100000 loops=1)"
(2) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=24) (actual time=0.008..38.702 rows=100000 loops=1)"
(3) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=24) (actual time=0.008..32.098 rows=100000 loops=1)"
(4) "Seq Scan on demo_bbb b  (cost=0.00..4425.00 rows=100000 width=232) (actual time=0.007..17.702 rows=100000 loops=1)"

Estou curioso para saber o que aconteceu, mas não consigo entender. Você poderia me dar algumas explicações de como foram essas consultas?

Observação: rastreei essas consultas usando o SQL Profiler (faça o download do Enterprisedb)

De PG DOC

pg_column_size(any): número de bytes usados ​​para armazenar um determinado valor (possivelmente compactado) pg_column_size mostra o espaço usado para armazenar qualquer valor de dado individual

Exemplo:

select pg_column_size(5::smallint);    -- 2 bytes 
select pg_column_size(5::int);         -- 4 bytes 

Com a entrada de pg_column_size, pode ser uma coluna ou linha, então criei um teste para verificar isso. Aqui está o meu teste:

Minha mesa

CREATE TABLE index_test
(
  id integer NOT NULL,  -- 4  bytes 
  a integer,            -- 4  bytes 
  b integer,            -- 4  bytes 
  CONSTRAINT index_test_id PRIMARY KEY (id)
)

1/ Primeira consulta: sum(pg_column_size(table.rows))

with abc as 
(
 select id,a,b
 from index_test where b > 100
)
select pg_size_pretty(sum(pg_column_size(abc.*))) from abc  -- "348 kB", abc.* = record

E a explicação da consulta:

"Aggregate  (cost=427.55..427.56 rows=1 width=24) (actual time=9.171..9.171 rows=1 loops=1)"
"  CTE abc"
"    ->  Seq Scan on index_test  (cost=0.00..180.00 rows=9902 width=12) (actual time=0.039..2.882 rows=9902 loops=1)"
"          Filter: (b > 100)"
"  ->  CTE Scan on abc  (cost=0.00..198.04 rows=9902 width=24) (actual time=0.047..7.151 rows=9902 loops=1)"
"Total runtime: 9.376 ms"

2/ Segunda consulta: sum(pg_column_size(table.id)) + sum(pg_column_size(table.a)) + sum(pg_column_size(table.b))

with abc as 
(
 select id, a, b 
 from index_test where b > 100
)
select  pg_size_pretty((sum(pg_column_size(id)))  + (sum(pg_column_size(b))) + (sum(pg_column_size(a))))
from abc  -- "116 kB"

E a explicação da consulta:

"Aggregate  (cost=526.57..526.59 rows=1 width=12) (actual time=10.959..10.959 rows=1 loops=1)"
"  CTE abc"
"    ->  Seq Scan on index_test  (cost=0.00..180.00 rows=9902 width=12) (actual time=0.035..2.780 rows=9902 loops=1)"
"          Filter: (b > 100)"
"  ->  CTE Scan on abc  (cost=0.00..198.04 rows=9902 width=12) (actual time=0.039..5.623 rows=9902 loops=1)"
"Total runtime: 11.173 ms"

3. Resultados:

Primeira consulta: 348 KB

Segunda consulta: 116 KB ( pg_column_size(id) = 39 KB ...)

Eu pensei que ambas as consultas deveriam retornar o mesmo resultado, mas tamanho da primeira consulta = 3 * tamanho da segunda consulta, isso me deixou confuso. Na primeira explicação, a "largura = 24 bytes/linha" (em vez de 12), me perguntei por que aumenta, acho que é a pista para a pergunta. Até agora, não consigo encontrar respostas claras para minha pergunta, por favor me ajude.

Eu tenho um nome de tabela " TABLE_A ( id integer, no integer) ".

Quero somar "não" com grupo por "id" e "soma de não" atual = "soma de não" anterior

Aqui está o meu código:

1/ Criar tabela e inserir dados:

create table table_a (id int, no int);

insert into table_a values(1, 10);
insert into table_a values(1, 20);
insert into table_a values(1, 30);
insert into table_a values(2, 100);
insert into table_a values(2, 200);
insert into table_a values(2, 300);
insert into table_a values(3, 1);
insert into table_a values(3, 2);
insert into table_a values(3, 3);
insert into table_a values(3, 3);

2/ Resultado desejado:

id | sum_of_no
--------------
1  | 60
2  | 660
3  | 669

3/ Minhas soluções (ok):

with t_report_code_temp as
(   
select id, sum(no) as t_code
from table_a
group by id 
)
select a.id, sum(b.t_code)
from t_report_code_temp a 
join t_report_code_temp b on b.id <= a.id
group by a.id
order by 1

Minha pergunta:

Você poderia me dar a melhor maneira de resolver?

Quando executo esta consulta no "enterprisedb 9.2", gera um erro:

select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::date;

Erro:

LINE 1: select quote_nullable(to_date('09-02-2014 ','dd-MM-yyyy'))::...
               ^
HINT:  Could not choose a best candidate function. You might need to add explicit type casts.
********** Error **********

ERROR: function quote_nullable(timestamp without time zone) is not unique
SQL state: 42725
Hint: Could not choose a best candidate function. You might need to add explicit type casts.
Character: 8

No Postgresql 9.1, esta query acima roda ok.

Por favor, me diga o que está acontecendo aqui. Por que a função quote_nullable(timestamp without time zone)não é única?

Quando eu create language plperlrecebo o erro: ERRO: não foi possível carregar a biblioteca "C:/Arquivos de Programas/PostgreSQL/9.1/lib/plperl.dll": O módulo especificado não pôde ser encontrado .

Mas no meu computador, o arquivo "plperl.dll" existe na pasta "C:/Program Files/PostgreSQL/9.1/lib/..." (não posso postar imagens ilustrativas, este fórum requer >= 10 reputações)

E se eu select * pg_pltemplate, eu recebo:

-[ RECORD 4 ]-+-------------------------
tmplname      | plperl
tmpltrusted   | t
tmpldbacreate | t
tmplhandler   | plperl_call_handler
tmplinline    | plperl_inline_handler
tmplvalidator | plperl_validator
tmpllibrary   | $libdir/plperl