Wrong index choice - Mailing list 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.