Wrong index choice

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.