Is the optimizer choice right?

[Carlos Benkendorf <carlosbenkendorf@xxxxxxxxxxxx>]

Hi,

 

We´re running 8.03 and I´m trying to understand why the following SELECT doesn´t use iarchave05 index.

 

If you disable seqscan then iarchave05 index is used and the total runtime is about 50% less than when iarchave05 index is not used.

 

Why is the optimizer not using iarchave05 index?

 select * from iparq.arript
 where
 (anocalc = 2005
 and rtrim(inscimob) = rtrim('010100101480010000')
 and codvencto2 = 1
 and parcela2 >= 0)
 or
 (anocalc = 2005
 and rtrim(inscimob) = rtrim('010100101480010000')
 and codvencto2 > 1)
 or
 (anocalc = 2005
 and rtrim(inscimob) > rtrim('010100101480010000'))
 or
 (anocalc > 2005)
 order by
 anocalc,
 inscimob,
 codvencto2,
 parcela2;

Explain analyze with  set enable_seqscan and enable_nestloop to on;
                                                                                                                                                                   &nbs p;                                  QUERY PLAN                                                                                                                                                                    & nbsp;&nb sp; 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=231852.08..232139.96 rows=115153 width=896) (actual time=38313.953..38998.019 rows=167601 loops=1)
   Sort Key: anocalc, inscimob, codvencto2, parcela2
   ->  Seq Scan on arript  (cost=0.00..170201.44 rows=115153 width=896) (actual time=56.979..13364.748 rows=167601 loops=1)
         Filter: (((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 = 1::numeric) AND (parcela2 >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 > 1::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) > '010100101480010000'::text)) OR (anocalc > 2005::numeric))
 Total runtime: 39247.521 ms
(5 rows)

 Sort  (cost=232243.19..232531.55 rows=115346 width=896) (actual time=46590.246..47225.910 rows=167601 loops=1)
   Sort Key: anocalc, inscimob, codvencto2, parcela2
   ->  Seq Scan on arript  (cost=0.00..170486.86 rows=115346 width=896) (actual time=54.573..13737.535 rows=167601 loops=1)
         Filter: (((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 = 1::numeric) AND (parcela2 >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 > 1::numeric)) OR ((anocalc = 2005::nu meric) AND (rtrim((inscimob)::text) > '010100101480010000'::text)) OR (anocalc > 2005::numeric))
 Total runtime: 47479.861 ms
(5 rows)

 Sort  (cost=232281.07..232569.48 rows=115365 width=896) (actual time=40856.792..41658.379 rows=167601 loops=1)
   Sort Key: anocalc, inscimob, codvencto2, parcela2
   ->  Seq Scan on arript  (cost=0.00..170515.00 rows=115365 width=896) (actual time=58.584..13529.589 rows=167601 loops=1)
         Filter: (((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 = 1::numeric) AND (parcela2 >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 > 1::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) > '010100101480010000'::text)) OR (anocalc > 2005::numeric))
 Total runtime: 41909.7 92 ms
(5 rows)

Explain analyze with  set enable_seqscan and enable_nestloop to off;
                                                                                                                                                                   &nb sp;  ;                             QUERY PLAN                                                                                                                                                                    & nbsp;&nb sp;    

 Index Scan using iarchave05 on arript  (cost=0.00..238964.80 rows=115255 width=896) (actual time=13408.139..19814.848 rows=167601 loops=1)
   Filter: (((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 = 1::numeric) AND (parcela2 >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 > 1::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) > '010100101480010000'::text)) OR (anocalc > 2005::numeric))
 Total runtime: 20110.892 ms
(3 rows)

 Index Scan using iarchave05 on arript  (cost=0.00..239091.81 rows=115320 width=896) (actual time=14238.672..21598.862 rows=167601 loops=1)
   Filter: (((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 = 1::numeric) AND (parcela2 >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 > 1::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) > '010100101480010000'::text)) OR (anocalc > 2005::numeric))
 Total runtime: 21967.840 ms
(3 rows)

 Index Scan using iarchave05 on arript  (cost=0.00..239115.06 rows=115331 width=896) (actual time=13863.863..20504.503 rows=167601 loops=1)
   Filter: (((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 = 1::numeric) AND (parcela2 >= 0::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) = '010100101480010000'::text) AND (codvencto2 > 1::numeric)) OR ((anocalc = 2005::numeric) AND (rtrim((inscimob)::text) > '010100101480010000'::text)) OR (anocalc > 2005::numeric))
 Total runtime: 20768.244 ms
(3 rows)

Table definition:
                 Table "iparq.arript"
      Column       |         Type          | Modifiers
-------------------+-----------------------+-----------
 anocalc           | numeric(4,0)          | not null
 cadastro          | numeric(8,0)          | not null
 codvencto         | numeric(2,0)          | not null
 parcela           | numeric(2,0)        &nbs p; | not null
 inscimob          | character varying(18) | not null
 codvencto2        | numeric(2,0)          | not null
 parcela2          | numeric(2,0)          | not null
 codpropr          | numeric(10,0)         | not null
 dtaven            | numeric(8,0)          | not null
...
...
...
Indexes:
    "pk_arript" PRIMARY KEY, btree (anocalc, cadastro, codvencto, parcela)
    "iarchave04" UNIQUE, btree (cadastro, anocalc, codvencto, parcela)
    "iarchave02" btree (inscimob, anocalc , codvencto2, parcela2)
    "iarchave03" btree (codpropr, dtaven)
    "iarchave05" btree (anocalc, inscimob, codvencto2, parcela2)

 

Thanks in advance!

 

Benkendorf

---

Yahoo! doce lar. Faça do Yahoo! sua homepage.