On Mon, Aug 30, 2010 at 12:51 PM, Ozer, Pam <pozer@xxxxxxxxxxxxxx> 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
