Re: Using Between

Yes.  ANALYZE was run after we loaded the data.  Thanks for your
assistance
Here is the full Query.

select distinct VehicleUsed.VehicleUsedId as VehicleUsedId ,
 VehicleUsed.VehicleUsedDisplayPriority as VehicleUsedDisplayPriority ,
 VehicleUsed.HasVehicleUsedThumbnail as HasVehicleUsedThumbnail ,
 VehicleUsed.HasVehicleUsedPrice as HasVehicleUsedPrice ,
 VehicleUsed.VehicleUsedPrice as VehicleUsedPrice ,
 VehicleUsed.HasVehicleUsedMileage as HasVehicleUsedMileage ,
 VehicleUsed.VehicleUsedMileage as VehicleUsedMileage ,
 VehicleUsed.IsCPO as IsCPO ,
 VehicleUsed.IsMTCA as IsMTCA
from VehicleUsed
inner join PostalCodeRegionCountyCity on ( lower (
VehicleUsed.PostalCode ) = lower ( PostalCodeRegionCountyCity.PostalCode
) )
where
( VehicleUsed.VehicleUsedPriceRangeFloor between 0 and 15000)
 and
( PostalCodeRegionCountyCity.RegionId = 26 )

order by VehicleUsed.VehicleUsedDisplayPriority ,
 VehicleUsed.HasVehicleUsedThumbnail desc ,
 VehicleUsed.HasVehicleUsedPrice desc ,
 VehicleUsed.VehicleUsedPrice ,
 VehicleUsed.HasVehicleUsedMileage desc ,
 VehicleUsed.VehicleUsedMileage ,
 VehicleUsed.IsCPO desc ,
 VehicleUsed.IsMTCA desc
limit 500000




Here is the explain Analyze

Limit  (cost=59732.41..60849.24 rows=44673 width=39) (actual
time=1940.274..1944.312 rows=2363 loops=1)
  Output: vehicleused.vehicleusedid,
vehicleused.vehicleuseddisplaypriority,
vehicleused.hasvehicleusedthumbnail, vehicleused.hasvehicleusedprice,
vehicleused.vehicleusedprice, vehicleused.hasvehicleusedmileage,
vehicleused.vehicleusedmileage, vehicleused.iscpo, vehicleused.ismtca
  ->  Unique  (cost=59732.41..60849.24 rows=44673 width=39) (actual
time=1940.272..1943.011 rows=2363 loops=1)
        Output: vehicleused.vehicleusedid,
vehicleused.vehicleuseddisplaypriority,
vehicleused.hasvehicleusedthumbnail, vehicleused.hasvehicleusedprice,
vehicleused.vehicleusedprice, vehicleused.hasvehicleusedmileage,
vehicleused.vehicleusedmileage, vehicleused.iscpo, vehicleused.ismtca
        ->  Sort  (cost=59732.41..59844.10 rows=44673 width=39) (actual
time=1940.270..1941.101 rows=2363 loops=1)
              Output: vehicleused.vehicleusedid,
vehicleused.vehicleuseddisplaypriority,
vehicleused.hasvehicleusedthumbnail, vehicleused.hasvehicleusedprice,
vehicleused.vehicleusedprice, vehicleused.hasvehicleusedmileage,
vehicleused.vehicleusedmileage, vehicleused.iscpo, vehicleused.ismtca
              Sort Key: vehicleused.vehicleuseddisplaypriority,
vehicleused.hasvehicleusedthumbnail, vehicleused.hasvehicleusedprice,
vehicleused.vehicleusedprice, vehicleused.hasvehicleusedmileage,
vehicleused.vehicleusedmileage, vehicleused.iscpo, vehicleused.ismtca,
vehicleused.vehicleusedid
              Sort Method:  quicksort  Memory: 231kB
              ->  Hash Join  (cost=289.85..55057.07 rows=44673 width=39)
(actual time=3.799..1923.958 rows=2363 loops=1)
                    Output: vehicleused.vehicleusedid,
vehicleused.vehicleuseddisplaypriority,
vehicleused.hasvehicleusedthumbnail, vehicleused.hasvehicleusedprice,
vehicleused.vehicleusedprice, vehicleused.hasvehicleusedmileage,
vehicleused.vehicleusedmileage, vehicleused.iscpo, vehicleused.ismtca
                    Hash Cond: (lower((vehicleused.postalcode)::text) =
lower((postalcoderegioncountycity.postalcode)::text))
                    ->  Seq Scan on vehicleused  (cost=0.00..51807.63
rows=402058 width=45) (actual time=0.016..1454.616 rows=398495 loops=1)
                          Output: vehicleused.vehicleusedid,
vehicleused.datasetid, vehicleused.vehicleusedproductid,
vehicleused.sellernodeid, vehicleused.vehicleyear,
vehicleused.vehiclemakeid, vehicleused.vehiclemodelid,
vehicleused.vehiclesubmodelid, vehicleused.vehiclebodystyleid,
vehicleused.vehicledoors, vehicleused.vehicleenginetypeid,
vehicleused.vehicledrivetrainid, vehicleused.vehicleexteriorcolorid,
vehicleused.hasvehicleusedthumbnail, vehicleused.postalcode,
vehicleused.vehicleusedprice, vehicleused.vehicleusedmileage,
vehicleused.buyerguid, vehicleused.vehicletransmissiontypeid,
vehicleused.vehicleusedintid, vehicleused.vehicleuseddisplaypriority,
vehicleused.vehicleusedsearchradius, vehicleused.vehiclejatoimagepath,
vehicleused.vehiclebodystylegroupid, vehicleused.productid,
vehicleused.productgroupid, vehicleused.vehiclevin,
vehicleused.vehicleclassgroupid,
vehicleused.vehiclegenericexteriorcolorid, vehicleused.highlight,
vehicleused.buyerid, vehicleused.dealerid,
vehicleused.hasvehicleusedprice, vehicleused.dealerstockid,
vehicleused.datesold, vehicleused.hasthumbnailimagepath,
vehicleused.vehicleinteriorcolorid, vehicleused.vehicleconditionid,
vehicleused.vehicletitletypeid, vehicleused.warranty,
vehicleused.thumbnailimagepath, vehicleused.fullsizeimagepath,
vehicleused.description, vehicleused.inserteddate,
vehicleused.feeddealerid, vehicleused.vehicleusedpricerangefloor,
vehicleused.vehicleusedmileagerangefloor,
vehicleused.hasvehicleusedmileage,
vehicleused.VehicleUsedIntId.distinct_count,
vehicleused.VehicleUsedPrice.average,
vehicleused.VehicleUsedId.distinct_count, vehicleused.iscpo,
vehicleused.ismtca, vehicleused.cpoprogramoemid,
vehicleused.cpoprogram3rdpartyid
                          Filter: ((vehicleusedpricerangefloor >= 0) AND
(vehicleusedpricerangefloor <= 15000))
                    ->  Hash  (cost=283.32..283.32 rows=522 width=6)
(actual time=1.084..1.084 rows=532 loops=1)
                          Output: postalcoderegioncountycity.postalcode
                          ->  Bitmap Heap Scan on
postalcoderegioncountycity  (cost=12.30..283.32 rows=522 width=6)
(actual time=0.092..0.361 rows=532 loops=1)
                                Output:
postalcoderegioncountycity.postalcode
                                Recheck Cond: (regionid = 26)
                                ->  Bitmap Index Scan on
postalcoderegioncountycity_i05  (cost=0.00..12.17 rows=522 width=0)
(actual time=0.082..0.082 rows=532 loops=1)
                                      Index Cond: (regionid = 26)
Total runtime: 1945.244 ms

-----Original Message-----
From: pgsql-performance-owner@postgresql.org
[mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Craig James
Sent: Friday, August 27, 2010 5:42 PM
To: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Using Between

On 8/27/10 5:21 PM, Ozer, Pam wrote:
> I have a query that
>
> Select Distinct VehicleId
>
>  From Vehicle
>
> Where VehicleMileage between 0 and 15000.
>
> I have an index on VehicleMileage. Is there another way to put an
index on a between? The index is not being picked up. It does get picked
up when I run
>
> Select Distinct VehicleId
>
>  From Vehicle
>
> Where VehicleMileage = 15000.
>
> I just want to make sure that there is not a special index I should be
using.

You need to post EXPLAIN ANALYZE of your query. It could be that an
index scan is actually not a good plan (for example, a sequential scan
might be faster if most of your vehicles have low mileage).  Without the
EXPLAIN ANALYZE, there's no way to say what's going on.

Did you ANALYZE your database after you loaded the data?

Craig

> Thanks
>
> *Pam Ozer*
>


--
Sent via pgsql-performance mailing list
(pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance