Skip site navigation (1) Skip section navigation (2)

Re: Use of BETWEEN with identical values

From: Robert Haas <robertmhaas(at)gmail(dot)com>
To: André Volpato <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Use of BETWEEN with identical values
Date: 2009-09-18 14:01:19
Message-ID: 603c8f070909180701n8421c0ft6baf25ff2dfbb967@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Thu, Sep 17, 2009 at 5:02 PM, André Volpato
<andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br> wrote:
> André Volpato escreveu:
>>
>> (...)
>>
>> (Postgres 8.3.6, Debian Linux 2.6.18-6-amd64)
>>
>> (...)
>
>> Condition 1:
>> # select fat_referencia from bds_contratacao_fatura where fat_referencia
>> BETWEEN 200908 AND 200908;
>> Index Scan using ibds_contratacao_fatura1 on bds_contratacao_fatura
>>  (cost=0.00..5.64 rows=1 width=4) (actual time=0.023..79.952 rows=163689
>> loops=1)
>>  Index Cond: ((fat_referencia >= 200908) AND (fat_referencia <= 200908))
>> Total runtime: 110.470 ms
>
>> Condition 3:
>> # select fat_referencia from bds_contratacao_fatura where fat_referencia =
>> 200908;
>> Index Scan using ibds_contratacao_fatura1 on bds_contratacao_fatura
>>  (cost=0.00..4745.07 rows=142940 width=4) (actual time=0.022..77.818
>> rows=163689 loops=1)
>>  Index Cond: (fat_referencia = 200908)
>> Total runtime: 108.292 ms
>>
>> I expect Postgres would give me the same plan in conditions 1 and 3.
>
> And also the core team...
>
> This behaviour is 8.3 related. In 8.4, conditions 1 and 3 results in the
> same plan.

Hmm.  I don't see anything in the release notes about it, but it's not
surprising that the optimizer would be improved in a newer version.

...Robert

In response to

pgsql-performance by date

Next:From: Michael KorbakovDate: 2009-09-18 14:21:29
Subject: Planner question - wrong row count estimation
Previous:From: Robert HaasDate: 2009-09-18 13:40:22
Subject: Re: Database performance post-VACUUM FULL

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group