Re: Using Between

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(at)gmail(dot)com]
Sent: Tuesday, September 21, 2010 12:35 PM
To: Ozer, Pam
Cc: Craig James; pgsql-performance(at)postgresql(dot)org
Subject: Re: [PERFORM] 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