Re: Using Between

On Mon, Aug 30, 2010 at 12:51 PM, Ozer, Pam <pozer(at)automotive(dot)com> wrote:
> 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

How many rows are in the vehicleused table in total?

Is your database small enough to fit in memory?

Do you have any non-default settings in postgresql.conf?

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company