There are 850,000 records in vehicleused. And the database is too big to be kept in memory.
Here are our config settings.
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
# (change requires restart)
bonjour_name = 'colapcnt1d' # defaults to the computer name
# (change requires restart)
shared_buffers = 500MB # min 128kB
effective_cache_size = 1000MB
log_destination = 'stderr' # Valid values are combinations of
logging_collector = on # Enable capturing of stderr and csvlog
datestyle = 'iso, mdy'
lc_messages = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
max_connections = 100
temp_buffers = 100MB
work_mem = 100MB
maintenance_work_mem = 500MB
max_files_per_process = 10000
seq_page_cost = 1.0
random_page_cost = 1.1
cpu_tuple_cost = 0.1
cpu_index_tuple_cost = 0.05
cpu_operator_cost = 0.01
default_statistics_target = 1000
autovacuum_max_workers = 1
#log_min_messages = DEBUG1
#log_min_duration_statement = 1000
#log_statement = all
#log_temp_files = 128
#log_lock_waits = on
#log_line_prefix = '%m %u %d %h %p %i %c %l %s'
#log_duration = on
#debug_print_plan = on
-----Original Message-----
From: Robert Haas [mailto:robertmhaas@xxxxxxxxx]
Sent: Tuesday, September 21, 2010 12:35 PM
To: Ozer, Pam
Cc: Craig James; pgsql-performance@xxxxxxxxxxxxxx
Subject: Re: Using Between
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
