pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedWrong index choice
3+ messages / 3 participants
[nested] [flat]
* Wrong index choice
@ 2010-09-29 16:55 Fabrício dos Anjos Silva <fabricio.silva@linkcom.com.br>
2010-09-29 21:00 ` Re: Wrong index choice Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-03 21:39 ` Re: Wrong index choice Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 3+ messages in thread
From: Fabrício dos Anjos Silva @ 2010-09-29 16:55 UTC (permalink / raw)
To: pgsql-performance
Hi,
I have this situation:
Database size: 7,6GB (on repository)
Memory size: 7,7GB
1 CPU: aprox. 2GHz Xeon
Number of tables: 1 (empresa)
CREATE TABLE "public"."empresa" (
"cdempresa" INTEGER NOT NULL,
"razaosocial" VARCHAR(180),
"cnpj" VARCHAR(14) NOT NULL,
"ie" VARCHAR(13),
"endereco" VARCHAR(150),
"numero" VARCHAR(40),
"complemento" VARCHAR(140),
"bairro" VARCHAR(80),
"municipio" VARCHAR(80),
"cep" VARCHAR(8),
"cxpostal" VARCHAR(12),
"telefone" VARCHAR(80),
"data" VARCHAR(10),
"ramo" VARCHAR(200),
"email" VARCHAR(80),
"uf" CHAR(10),
"origem" VARCHAR(30),
"nomefantasia" VARCHAR(120),
"site" VARCHAR(80),
"dtatualizacao" TIMESTAMP WITHOUT TIME ZONE,
"areautil" VARCHAR(8),
"ramosecundario" VARCHAR(200),
"observacao" VARCHAR(120),
"natureza" VARCHAR(80),
"situacao" VARCHAR(80),
"cdramo" INTEGER,
"cdramorf" INTEGER,
"ramo3" VARCHAR(200),
"ramo4" VARCHAR(200),
"ramo5" VARCHAR(200),
"ramo6" VARCHAR(200),
"fonte" VARCHAR(12),
"dtcriacao" DATE,
"cdramorf2" INTEGER,
"ramo7" VARCHAR(200),
"ramo8" VARCHAR(200),
"ramo9" VARCHAR(200),
"ramo10" VARCHAR(200),
"razaosocialts" TSVECTOR,
"latitude" DOUBLE PRECISION,
"longitude" DOUBLE PRECISION,
"precisao" VARCHAR(1),
CONSTRAINT "pk_empresa" PRIMARY KEY("cdempresa")
) WITHOUT OIDS;
CREATE INDEX "idx_cnpj" ON "public"."empresa"
USING btree ("cnpj");
CREATE INDEX "idx_empresa_dtcriacao" ON "public"."empresa"
USING btree ("dtcriacao");
alter table empresa alter column cnpj set statistics 1000;
analyze verbose empresa (cnpj);
INFO: "empresa": scanned 300000 of 514508 pages, containing 5339862 live
rows and 0 dead rows; 300000 rows in sample, 9158006 estimated total rows
alter table empresa alter column dtcriacao set statistics 1000;
analyze verbose empresa (dtcriacao);
INFO: "empresa": scanned 300000 of 514508 pages, containing 5342266 live
rows and 0 dead rows; 300000 rows in sample, 9162129 estimated total rows
shared_buffers = 2000MB
work_mem = 64MB
maintenance_work_mem = 256MB
effective_io_concurrency = 4 (using RAID-0 on 4 disks)
seq_page_cost = 0.01
random_page_cost = 0.01
cpu_tuple_cost = 0.003
cpu_index_tuple_cost = 0.001
cpu_operator_cost = 0.0005
effective_cache_size = 7200MB
geqo_threshold = 15
All data and metadata required for the following queries are already in
OS cache.
When enable_indexscan is off, I execute the same query 3 times, altering how
much data I want to query (check the current_date-X part). In this scenario,
I get the following plans:
explain analyze select max(cnpj) from empresa where dtcriacao >=
current_date-3;
QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate (cost=18.30..18.30 rows=1 width=15) (actual time=50.075..50.076
rows=1 loops=1)
-> Bitmap Heap Scan on empresa (cost=1.15..17.96 rows=682 width=15)
(actual time=36.252..47.264 rows=1985 loops=1)
Recheck Cond: (dtcriacao >= (('now'::text)::date - 3))
-> Bitmap Index Scan on idx_empresa_dtcriacao (cost=0.00..1.12
rows=682 width=0) (actual time=35.980..35.980 rows=1985 loops=1)
Index Cond: (dtcriacao >= (('now'::text)::date - 3))
Total runtime: 50.193 ms
(6 rows)
explain analyze select max(cnpj) from empresa where dtcriacao >=
current_date-4;
QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate (cost=36.31..36.31 rows=1 width=15) (actual time=41.880..41.881
rows=1 loops=1)
-> Bitmap Heap Scan on empresa (cost=2.25..35.63 rows=1364 width=15)
(actual time=23.291..38.146 rows=2639 loops=1)
Recheck Cond: (dtcriacao >= (('now'::text)::date - 4))
-> Bitmap Index Scan on idx_empresa_dtcriacao (cost=0.00..2.18
rows=1364 width=0) (actual time=22.946..22.946 rows=2639 loops=1)
Index Cond: (dtcriacao >= (('now'::text)::date - 4))
Total runtime: 42.025 ms
(6 rows)
explain analyze select max(cnpj) from empresa where dtcriacao >=
current_date-5;
QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate (cost=54.13..54.13 rows=1 width=15) (actual time=93.265..93.266
rows=1 loops=1)
-> Bitmap Heap Scan on empresa (cost=3.35..53.11 rows=2045 width=15)
(actual time=26.749..84.553 rows=6380 loops=1)
Recheck Cond: (dtcriacao >= (('now'::text)::date - 5))
-> Bitmap Index Scan on idx_empresa_dtcriacao (cost=0.00..3.24
rows=2045 width=0) (actual time=26.160..26.160 rows=6380 loops=1)
Index Cond: (dtcriacao >= (('now'::text)::date - 5))
Total runtime: 93.439 ms
(6 rows)
Note that the plan is the same for all 3 queries.
However, when enable_indexscan is on, I execute the same 3 queries, and I
get the following plans:
explain analyze select max(cnpj) from empresa where dtcriacao >=
current_date-3;
QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate (cost=17.14..17.14 rows=1 width=15) (actual time=35.960..35.961
rows=1 loops=1)
-> Index Scan using idx_empresa_dtcriacao on empresa (cost=0.00..16.80
rows=682 width=15) (actual time=0.078..23.215 rows=1985 loops=1)
Index Cond: (dtcriacao >= (('now'::text)::date - 3))
Total runtime: 36.083 ms
(4 rows)
explain analyze select max(cnpj) from empresa where dtcriacao >=
current_date-4;
QUERY PLAN
-----------------------------------------------------------------------------------------
Aggregate (cost=34.20..34.20 rows=1 width=15) (actual time=40.625..40.626
rows=1 loops=1)
-> Index Scan using idx_empresa_dtcriacao on empresa (cost=0.00..33.52
rows=1364 width=15) (actual time=0.071..37.019 rows=2639 loops=1)
Index Cond: (dtcriacao >= (('now'::text)::date - 4))
Total runtime: 40.740 ms
(4 rows)
explain analyze select max(cnpj) from empresa where dtcriacao >=
current_date-5;
QUERY
PLAN
-----------------------------------------------------------------------------------------
Result (cost=32.24..32.24 rows=1 width=0) (actual time=5223.937..5223.938
rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..32.24 rows=1 width=15) (actual
time=5223.921..5223.922 rows=1 loops=1)
-> Index Scan Backward using idx_cnpj on empresa
(cost=0.00..65925.02 rows=2045 width=15) (actual time=5223.913..5223.913
rows=1 loops=1)
Index Cond: ((cnpj)::text IS NOT NULL)
Filter: (dtcriacao >= (('now'::text)::date - 5))
Total runtime: 5224.037 ms
(7 rows)
Note that when I subtract at least 5 from current_date, the plan is
changed to an Index Scan Backward on idx_cnpj, which is a worse choice.
My question is: Why the cost of Limit on the last query, estimated as
32.24 if the Index Scan Backward is estimated at 65925.02? Since there is a
filter based on column dtcriacao, the whole index is going to be analyzed,
and Limit is going to wait for the complete Index Scan to complete. Why use
idx_cnpj in this case? Why not use idx_empresa_dtcriacao?
Just for comparison, consider this query:
explain analyze select max(cnpj) from empresa;
QUERY
PLAN
-----------------------------------------------------------------------------------------
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.133..0.134 rows=1
loops=1)
InitPlan 1 (returns $0)
-> Limit (cost=0.00..0.00 rows=1 width=15) (actual time=0.120..0.121
rows=1 loops=1)
-> Index Scan Backward using idx_cnpj on empresa
(cost=0.00..42146.33 rows=9162129 width=15) (actual time=0.114..0.114 rows=1
loops=1)
Index Cond: ((cnpj)::text IS NOT NULL)
Total runtime: 0.212 ms
(6 rows)
In this case, it is correct to use the Index Scan Backward on idx_cnpj,
since the Limit will interrupt it just after the first returned value. The
estimated Limit cost of 0.00 is ok, even if the Scan cost is estimated at
42146.33.
I only managed to get all data in OS cache after mounting this new server
with 7,7GB of memory, which I can't afford to use permanently. My real
server has only 1,7GB of memory and this Index Scan Backward plan takes
forever to run (I really don't know how much time), making tons of random
seeks. When enable_indexscan id off, the query runs quickly on the 1,7GB
server.
Initially I was using statistics for these 2 columns as 200 and 300, but
even after changing to 1000, the problem persists. I tried several different
values the seq_page_cost, random_page_cost, cpu_tuple_cost,
cpu_index_tuple_cost and cpu_operator_cost with no success.
Could someone explain me this?
Thanks,
Fabrício dos Anjos Silva
LinkCom Soluções em T.I.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wrong index choice
2010-09-29 16:55 Wrong index choice Fabrício dos Anjos Silva <fabricio.silva@linkcom.com.br>
@ 2010-09-29 21:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 3+ messages in thread
From: Kevin Grittner @ 2010-09-29 21:00 UTC (permalink / raw)
To: Fabrício dos Anjos Silva <fabricio.silva@linkcom.com.br>; pgsql-performance
Fabrício dos Anjos Silva<fabricio.silva@linkcom.com.br> wrote:
> explain analyze select max(cnpj) from empresa where dtcriacao >=
> current_date-5;
> Result (cost=32.24..32.24 rows=1 width=0) (actual
> time=5223.937..5223.938 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.00..32.24 rows=1 width=15) (actual
> time=5223.921..5223.922 rows=1 loops=1)
> -> Index Scan Backward using idx_cnpj on empresa
> (cost=0.00..65925.02 rows=2045 width=15) (actual
> time=5223.913..5223.913 rows=1 loops=1)
> Index Cond: ((cnpj)::text IS NOT NULL)
> Filter: (dtcriacao >= (('now'::text)::date - 5))
> Total runtime: 5224.037 ms
> My question is: Why the cost of Limit on the last query, estimated
> as 32.24 if the Index Scan Backward is estimated at 65925.02?
If you divide the total cost for the step by the number of rows it
would take to read all the way through, you get 32.24; so it clearly
expects to find a row which matches the filter condition right away.
(Or it fails to consider the fact that the filter condition could
cause it to read multiple rows looking for a match.)
> Since there is a filter based on column dtcriacao, the whole index
> is going to be analyzed, and Limit is going to wait for the
> complete Index Scan to complete.
Only if there are no matching rows. Since you're asking for the
max, if it reads in descending sequence on the index, it can stop as
soon as it finds one matching row.
-Kevin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Wrong index choice
2010-09-29 16:55 Wrong index choice Fabrício dos Anjos Silva <fabricio.silva@linkcom.com.br>
@ 2010-10-03 21:39 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2010-10-03 21:39 UTC (permalink / raw)
To: Fabrício dos Anjos Silva <fabricio.silva@linkcom.com.br>; +Cc: pgsql-performance
=?ISO-8859-1?Q?Fabr=EDcio_dos_Anjos_Silva?= <fabricio.silva@linkcom.com.br> writes:
> explain analyze select max(cnpj) from empresa where dtcriacao >=
> current_date-5;
> QUERY
> PLAN
> -----------------------------------------------------------------------------------------
> Result (cost=32.24..32.24 rows=1 width=0) (actual time=5223.937..5223.938
> rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.00..32.24 rows=1 width=15) (actual
> time=5223.921..5223.922 rows=1 loops=1)
> -> Index Scan Backward using idx_cnpj on empresa
> (cost=0.00..65925.02 rows=2045 width=15) (actual time=5223.913..5223.913
> rows=1 loops=1)
> Index Cond: ((cnpj)::text IS NOT NULL)
> Filter: (dtcriacao >= (('now'::text)::date - 5))
> Total runtime: 5224.037 ms
> (7 rows)
BTW, a large part of the reason that it's switching to this plan type
too soon is that you've got random_page_cost set really small:
> seq_page_cost = 0.01
> random_page_cost = 0.01
I think you need to back those off by an order of magnitude or so.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2010-10-03 21:39 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-09-29 16:55 Wrong index choice Fabrício dos Anjos Silva <fabricio.silva@linkcom.com.br>
2010-09-29 21:00 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2010-10-03 21:39 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox