Olá, O tempo para as 2 consultas foi 12 ms na verdade. Com os recursos atuais de computação, para você poder ver "a olho nu" uma consulta com índices ser mais rápida que uma consulta "full table scan", só com consultas complexas ou tabelas com milhões de registros. Marçal de Lima Hokama ---------------------
________________________________ > Date: Sat, 2 Jan 2010 14:51:32 -0800 > From: [email protected] > To: [email protected] > Subject: Re: [pgbr-geral] Índices e otimização > > > > 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: Re: Índices e otimização > > Sent from the PostgreSQL - Brasil mailing list archive at Nabble.com. > _________________________________________________________________ Fique protegido de ameças utilizando o Novo Internet Explorer 8. Baixe já, é grátis! http://brasil.microsoft.com.br/IE8/mergulhe/?utm_source=MSN%3BHotmail&utm_medium=Tagline&utm_content=Tag1&utm_campaign=IE8 _______________________________________________ pgbr-geral mailing list [email protected] https://listas.postgresql.org.br/cgi-bin/mailman/listinfo/pgbr-geral
