Olá,
2010/1/2 moisespsena <[email protected]>
> 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
>
>
> Você percebeu que o tempo de execução é 12 millisegundos e não de 12
segundos?
------------------------------
> View this message in context: Re: Índices e
> otimização<http://old.nabble.com/%C3%8Dndices-e-otimiza%C3%A7%C3%A3o-tp26994726p26997275.html>
>
> Sent from the PostgreSQL - Brasil mailing list
> archive<http://old.nabble.com/PostgreSQL---Brasil-f15652.html>at Nabble.com.
>
>
> _______________________________________________
> pgbr-geral mailing list
> [email protected]
> https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
>
>
[]s
--
JotaComm
http://jotacomm.wordpress.com
_______________________________________________
pgbr-geral mailing list
[email protected]
https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
