Buenas amigos
eu tenho uma pequena tabela no postgresql (populada em 500000 registros para
testa com a seguinte estrutura
CREATE TABLE test_table
(
code_id character(8),
descr character varying(50),
days numeric(8) DEFAULT 0,
date_lim date,
sr_recno numeric(15) NOT NULL DEFAULT nextval('test_table_sq'::regclass),
sr_deleted character(1) NOT NULL DEFAULT ' '::bpchar,
CONSTRAINT test_table_sr_recno_key UNIQUE (sr_recno)
)
WITH (OIDS=FALSE);
ALTER TABLE test_table OWNER TO postgres;
CREATE INDEX test_table_ind01_000001
ON test_table
USING btree
(code_id, descr, sr_recno);
CREATE INDEX test_table_ind02_000002
ON test_table
USING btree
(days, date_lim, sr_recno);
CREATE INDEX test_table_ind03_000003
ON test_table
USING btree
(code_id, descr, days, sr_recno);
CREATE INDEX test_table_sr
ON test_table
USING btree
(sr_recno);
rodando o explain na query abaixo
SELECT A.code_id, A.descr, A.percent, A.days, A.sr_recno, A.sr_deleted FROM
test_table A WHERE ( ( A.code_id >= '00000002' AND A.descr >= 'Hello,
SQL!' AND A.days >= 499998 AND A.sr_recno >= 2) OR ( A.code_id >=
'00000002' AND A.descr = 'Hello, SQL!' AND A.days > 499998 ) OR (
A.code_id = '00000002' AND A.descr > 'Hello, SQL!' ) OR ( A.code_id >
'00000002' ) ) ORDER BY A.code_id, A.descr, A.days, A.sr_recno LIMIT
32
e retornado
"Limit (cost=0.00..5.91 rows=32 width=70) (actual time=0.083..0.222 rows=32
loops=1)"
" -> Index Scan using test_table_ind03_000003 on test_table a
(cost=0.00..92307.51 rows=499950 width=70) (actual time=0.078..0.178 rows=32
loops=1)"
" Filter: (((code_id >= '00000002'::bpchar) AND ((descr)::text >=
'Hello, SQL!'::text) AND (days >= 499998::numeric) AND (sr_recno >=
2::numeric)) OR ((code_id >= '00000002'::bpchar) AND ((descr)::text =
'Hello, SQL!'::text) AND (days > 499998::numeric)) OR ((code_id =
'00000002'::bpchar) AND ((descr)::text > 'Hello, SQL!'::text)) OR (code_id >
'00000002'::bpchar))"
"Total runtime: 0.347 ms"
essa query esta gerando um full table scan
ja que usando essa query abaixo que faz a mesma coisa
SELECT * FROM (SELECT A."code_id", A."descr", A."percent", A."days",
A."sr_recno", A."sr_deleted" FROM "test_table" A WHERE ( A."code_id" >=
'00000002' AND A."descr" >= 'Hello, SQL!' AND A."days" >= 499998 AND
A."sr_recno" >= 2 ) ORDER BY A."code_id", A."descr", A."days",
A."sr_recno" LIMIT 32 ) TMP1
UNION
SELECT * FROM (SELECT A."code_id", A."descr", A."percent", A."days",
A."sr_recno", A."sr_deleted" FROM "test_table" A WHERE ( A."code_id" >=
'00000002' AND A."descr" = 'Hello, SQL!' AND A."days" > 499998 ) ORDER BY
A."code_id", A."descr", A."days", A."sr_recno" LIMIT 32 ) TMP2
UNION
SELECT * FROM (SELECT A."code_id", A."descr", A."percent", A."days",
A."sr_recno", A."sr_deleted" FROM "test_table" A WHERE ( A."code_id" =
'00000002' AND A."descr" > 'Hello, SQL!' ) ORDER BY A."code_id",
A."descr", A."days", A."sr_recno" LIMIT 32 ) TMP3
UNION
SELECT * FROM (SELECT A."code_id", A."descr", A."percent", A."days",
A."sr_recno", A."sr_deleted" FROM "test_table" A WHERE ( A."code_id" >
'00000002' ) ORDER BY A."code_id", A."descr", A."days", A."sr_recno"
LIMIT 32 ) TMP4 ORDER BY "code_id", "descr", "days", "sr_recno" LIMIT
32
eu tenho como resultado do explain
"Limit (cost=208.67..208.75 rows=32 width=101)"
" -> Sort (cost=208.67..208.76 rows=35 width=101)"
" Sort Key: code_id, descr, days, sr_recno"
" -> Unique (cost=207.16..207.78 rows=35 width=101)"
" -> Sort (cost=207.16..207.25 rows=35 width=101)"
" Sort Key: code_id, descr, percent, days, sr_recno,
sr_deleted"
" -> Append (cost=96.01..206.27 rows=35 width=101)"
" -> Limit (cost=96.01..96.02 rows=1 width=70)"
" -> Sort (cost=96.01..96.02 rows=1
width=70)"
" Sort Key: a.code_id, a.descr, a.days,
a.sr_recno"
" -> Index Scan using
test_table_ind02_000002 on test_table a (cost=0.00..96.00 rows=1 width=70)"
" Index Cond: ((days >=
499998::numeric) AND (sr_recno >= 2::numeric))"
" Filter: ((code_id >=
'00000002'::bpchar) AND ((descr)::text >= 'Hello, SQL!'::text))"
" -> Limit (cost=95.89..95.89 rows=1 width=70)"
" -> Sort (cost=95.89..95.89 rows=1
width=70)"
" Sort Key: a.code_id, a.descr, a.days,
a.sr_recno"
" -> Index Scan using
test_table_ind02_000002 on test_table a (cost=0.00..95.88 rows=1 width=70)"
" Index Cond: (days >
499998::numeric)"
" Filter: ((code_id >=
'00000002'::bpchar) AND ((descr)::text = 'Hello, SQL!'::text))"
" -> Limit (cost=0.00..8.47 rows=1 width=70)"
" -> Index Scan using
test_table_ind03_000003 on test_table a (cost=0.00..8.47 rows=1 width=70)"
" Index Cond: ((code_id =
'00000002'::bpchar) AND ((descr)::text > 'Hello, SQL!'::text))"
" -> Limit (cost=0.00..5.19 rows=32 width=70)"
" -> Index Scan using
test_table_ind03_000003 on test_table a (cost=0.00..81054.33 rows=499950
width=70)"
" Index Cond: (code_id >
'00000002'::bpchar)"
Atenciosamente
Luiz
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
