Re: BETWEEN optimizer problems with single-value

From: Tom Lane
Subject: Re: BETWEEN optimizer problems with single-value
Date: ,
Msg-id: 28798.1142450270@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: BETWEEN optimizer problems with single-value  ("Kevin Grittner")
Responses: Re: BETWEEN optimizer problems with single-value  ("Kevin Grittner")
Re: BETWEEN optimizer problems with single-value  (Simon Riggs)
List: pgsql-performance

Tree view

BETWEEN optimizer problems with single-value range  ("Kevin Grittner", )
 Re: BETWEEN optimizer problems with single-value range  (Andreas Kretschmer, )
  Re: BETWEEN optimizer problems with single-value  ("Kevin Grittner", )
   Re: BETWEEN optimizer problems with single-value  (Tom Lane, )
    Re: BETWEEN optimizer problems with single-value  ("Kevin Grittner", )
    Re: BETWEEN optimizer problems with single-value  (Simon Riggs, )
     Re: BETWEEN optimizer problems with single-value  (Simon Riggs, )
      Re: BETWEEN optimizer problems with single-value  (Tom Lane, )
       Re: BETWEEN optimizer problems with single-value  (Simon Riggs, )
        Re: BETWEEN optimizer problems with single-value  (Tom Lane, )
         Re: BETWEEN optimizer problems with single-value  (Simon Riggs, )
          Re: BETWEEN optimizer problems with single-value  (Alvaro Herrera, )
           Re: BETWEEN optimizer problems with single-value  (Simon Riggs, )
          Re: BETWEEN optimizer problems with single-value  (Tom Lane, )
           Re: BETWEEN optimizer problems with single-value  (Simon Riggs, )
 Re: BETWEEN optimizer problems with single-value range  ("Merlin Moncure", )
  Re: BETWEEN optimizer problems with single-value range  (Andreas Kretschmer, )
   Re: BETWEEN optimizer problems with single-value range  ("Merlin Moncure", )
 Re: [HACKERS] BETWEEN optimizer problems with single-value range  (Simon Riggs, )
  Re: [HACKERS] BETWEEN optimizer problems with single-value  ("Kevin Grittner", )

"Kevin Grittner" <> writes:
>> Odd. Can you tell us your PG- Version?

> this is 8.1.2 with some 8.1.3 changes plus the string literal patch.)

8.1 is certainly capable of devising the plan you want, for example
in the regression database:

regression=# explain select * from tenk1 where thousand = 10 and tenthous between 42 and 144;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Index Scan using tenk1_thous_tenthous on tenk1  (cost=0.00..6.01 rows=1 width=244)
   Index Cond: ((thousand = 10) AND (tenthous >= 42) AND (tenthous <= 144))
(2 rows)

It looks to me like this is a matter of bad cost estimation, ie, it's
thinking the other index is cheaper to use.  Why that is is not clear.
Can we see the pg_stats rows for ctofcNo and calDate?

Also, try to force it to generate the plan you want, so we can see what
it thinks the cost is for that.  If you temporarily drop the wrong index
you should be able to get there:

    begin;
    drop index  "Cal_CalDate";
    explain analyze select ... ;
    -- repeat as needed if it chooses some other wrong index
    rollback;

I hope you have a play copy of the database to do this in ---
although it would be safe to do the above in a live DB, the DROP would
exclusive-lock the table until you finish the experiment and rollback,
which probably is not good for response time ...

            regards, tom lane


pgsql-performance by date:

From: Jan de Visser
Date:
Subject: Re: Slow SELECTS after large update cycle
From: Tom Lane
Date:
Subject: Re: BETWEEN optimizer problems with single-value