JotaComm wrote:
>
> Qual a versão do seu PostgreSQL?
>
Postgres Studio Advanced Server 8.3R2
JotaComm wrote:
>
> Você pode colocar aqui a definição da tabela?
>
CREATE TABLE "public"."tb2" (
"idtb2" BIGSERIAL,
"idtb1" BIGINT NOT NULL,
"sobre" VARCHAR(20),
CONSTRAINT "tb2_pkey" PRIMARY KEY("idtb2")
);
CREATE INDEX "tb2_idx" ON "public"."tb2"
USING btree ("idtb1");
CREATE TABLE "public"."tb1" (
"idtb1" BIGSERIAL,
"nome" VARCHAR(20),
CONSTRAINT "tb1_pkey" PRIMARY KEY("idtb1")
);
JotaComm wrote:
>
>
> Veja o meu exemplo:
>
> Criação da tabela.
>
> postgres=# CREATE TABLE tab1(codigo INTEGER);
> CREATE TABLE
>
> Inserção de registros.
>
> postgres=# INSERT INTO tab1 VALUES (generate_series(1,600000));
> INSERT 0 600000
>
> postgres=# SELECT count(*) FROM tab1;
> count
> --------
> 600000
> (1 row)
>
> postgres=# SELECT * FROM tab1 WHERE codigo=500;
> codigo
> --------
> 500
> (1 row)
>
> Time: 71,537 ms --Tempo da execução da consulta.
>
> postgres=# postgres=# EXPLAIN SELECT * FROM tab1 WHERE codigo=500;
> QUERY PLAN
> --------------------------------------------------------
> Seq Scan on tab1 (cost=0.00..10155.00 rows=1 width=4)
> Filter: (codigo = 500)
> (2 rows)
>
> Time: 0,643 ms --Tempo da execução do comando EXPLAIN.
>
> Adição de uma PK. Ao fazer isso automaticamente um índice será criado.
>
> postgres=# ALTER TABLE tab1 ADD PRIMARY KEY(codigo);
> NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index
> "tab1_pkey" for table "tab1"
> ALTER TABLE
>
> postgres=# postgres=# SELECT * FROM tab1 WHERE codigo=500;
> codigo
> --------
> 500
> (1 row)
>
> Time: 0,851 ms --Tempo da execução da consulta.
>
> postgres=# EXPLAIN SELECT * FROM tab1 WHERE codigo=500;
> QUERY PLAN
> ----------------------------------------------------------------------
> Index Scan using tab1_pkey on tab1 (cost=0.00..8.33 rows=1 width=4)
> Index Cond: (codigo = 500)
> (2 rows)
>
> Time: 1,391 ms --Tempo da execução do comando EXPLAIN.
>
> Faça este teste e se o resultado for diferente do apresentado aqui, poste
> o
> seu resultado.
>
>
Recriei as tabelas e inserir novos registros:
-- registros de tb1
INSERT INTO tb1 (nome) VALUES ('nome_' || generate_series(1,600000));
-- registros de tb2 com idtb1 = 2
INSERT INTO tb2 (idtb1, sobre) VALUES (2, 'sobre_' ||
generate_series(1,600000));
-- registros de tb2 com idtb1 = 50
INSERT INTO tb2 (idtb1, sobre) VALUES (50, 'sobre_' ||
generate_series(1,600000));
-- contar registros de tb1
SELECT count(idtb1) FROM tb1;
> count(idtb1)
> --------------
> 600000
> (1 row)
-- contar registros de tb2
SELECT count(idtb2) FROM tb2;
> count(idtb1)
> --------------
> 1200000
> (1 row)
Executei o VACUM do PGAdmin nas duas tabelas:
TB1:
INFO: vacuuming "public.tb1"INFO: "tb1": found 0 removable, 600000
nonremovable row versions in 3822 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 39 to 44 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 18888 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 3592 free bytes are potential move destinations.
CPU 0.09s/0.18u sec elapsed 1.58 sec.
INFO: index "tb1_pkey" now contains 600000 row versions in 1648 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "tb1": moved 0 row versions, truncated 3822 to 3822 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.tb1"INFO: "tb1": scanned 3000 of 3822 pages,
containing 470946 live rows and 0 dead rows; 3000 rows in sample, 599985
estimated total rowsTempo total de execução da consulta: 1704 ms.
TB2:
INFO: vacuuming "public.tb2"INFO: "tb2": found 0 removable, 1200000
nonremovable row versions in 8824 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 48 to 53 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 74576 bytes.
0 pages are or will become empty, including 0 at the end of the table.
1 pages containing 3968 free bytes are potential move destinations.
CPU 0.23s/0.59u sec elapsed 3.25 sec.INFO: index "tb2_pkey" now contains
1200000 row versions in 3293 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: index "tb2_idx" now contains
1200000 row versions in 4172 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.00 sec.
INFO: "tb2": moved 0 row versions, truncated 8824 to 8824 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.INFO: analyzing
"public.tb2"INFO: "tb2": scanned 3000 of 8824 pages, containing 407934 live
rows and 0 dead rows; 3000 rows in sample, 1199870 estimated total rowsTempo
total de execução da consulta: 3425 ms.
Um registro de tb1:
SELECT * FROM tb1 WHERE idtb1 = 50;
idtb1 | nome
--------------
50 | nome_50
(1 row)
Time: 11ms
Um registro de tb2:
SELECT * FROM tb2 WHERE idtb2 = 50;
idtb2| idtb1 | sobre
--------------
50 | 2 | sobre_50
(1 row)
Time: 12ms
EXPLAIN de tb1:
EXPLAIN SELECT * FROM tb1 WHERE idtb1 = 500;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using tb1_pkey on tb1 (cost=0.00..8.33 rows=1 width=19)
Index Cond: (idtb1 = 500)
(2 rows)
Time: 12ms
EXPLAIN de tb2:
EXPLAIN SELECT * FROM tb2 WHERE idtb2 = 500;
QUERY PLAN
----------------------------------------------------------------------
Index Scan using tb2_pkey on tb2 (cost=0.00..8.40 rows=1 width=28)
Index Cond: (idtb2 = 500)
(2 rows)
Time: 12ms
--
View this message in context:
http://old.nabble.com/%C3%8Dndices-e-otimiza%C3%A7%C3%A3o-tp26994726p26997275.html
Sent from the PostgreSQL - Brasil mailing list archive at Nabble.com.
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
