On Tuesday, October 9, 2012 1:40:08 AM UTC+5:30, Steve Crawford wrote: > On 10/08/2012 08:26 AM, Navaneethan R wrote: > > > Hi all, > > > > > > I have 10 million records in my postgres table.I am running the database in amazon ec2 medium instance. I need to access the last week data from the table. > > > It takes huge time to process the simple query.So, i throws time out exception error. > > > > > > query is : > > > select count(*) from dealer_vehicle_details where modified_on between '2012-10-01' and '2012-10-08' and dealer_id=270001; > > > > > > After a lot of time it responds 1184 as count > > > > > > what are the ways i have to follow to increase the performance of this query? > > > > > > The insertion also going parallel since the daily realtime updation. > > > > > > what could be the reason exactly for this lacking performace? > > > > > > > > What version of PostgreSQL? You can use "select version();" and note > > that 9.2 has index-only scans which can result in a substantial > > performance boost for queries of this type. > > > > What is the structure of your table? You can use "\d+ > > dealer_vehicle_details" in psql. > > > > Have you tuned PostgreSQL in any way? If so, what? > > > > Cheers, > > Steve > > > > > > -- > > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-performance version(): PostgreSQL 8.4.8 on i686-pc-linux-gnu, compiled by GCC gcc-4.5.real (Ubuntu/Linaro 4.5.2-8ubuntu4) 4.5.2, 32-bit Desc: Table "public.dealer_vehicle_details" Column | Type | Modifiers | Storage | Description ----------------+--------------------------+-------------------------------------------------------------------------+---------+------------- id | integer | not null default nextval('dealer_vehicle_details_new_id_seq'::regclass) | plain | vin_id | integer | not null | plain | vin_details_id | integer | | plain | price | integer | | plain | mileage | double precision | | plain | dealer_id | integer | not null | plain | created_on | timestamp with time zone | not null | plain | modified_on | timestamp with time zone | not null | plain | Indexes: "dealer_vehicle_details_pkey" PRIMARY KEY, btree (id) "idx_dealer_sites_id" UNIQUE, btree (id) WHERE dealer_id = 270001 "idx_dealer_sites_id_526889" UNIQUE, btree (id) WHERE dealer_id = 526889 "idx_dealer_sites_id_9765" UNIQUE, btree (id, vin_id) WHERE dealer_id = 9765 "idx_dealer_sites_id_9765_all" UNIQUE, btree (id, vin_id, price, mileage, modified_on, created_on, vin_details_id) WHERE dealer_id = 9765 "mileage_idx" btree (mileage) "price_idx" btree (price) "vehiclecre_idx" btree (created_on) "vehicleid_idx" btree (id) "vehiclemod_idx" btree (modified_on) "vin_details_id_idx" btree (vin_details_id) "vin_id_idx" btree (vin_id) Foreign-key constraints: "dealer_vehicle_master_dealer_id_fkey" FOREIGN KEY (dealer_id) REFERENCES dealer_dealer_master(id) DEFERRABLE INITIALLY DEFERRED "dealer_vehicle_master_vehicle_id_fkey" FOREIGN KEY (vin_id) REFERENCES dealer_vehicle(id) DEFERRABLE INITIALLY DEFERRED "dealer_vehicle_master_vin_details_id_fkey" FOREIGN KEY (vin_details_id) REFERENCES vin_lookup_table(id) DEFERRABLE INITIALLY DEFERRED Has OIDs: no After created the index for WHERE clause "WHERE dealer_id = 270001"..It is performing better.I have more dealer ids Should I do it for each dealer_id? And The insertion service also happening background parallel. So, What are the important steps I should follow frequently to keep the database healthy? Since, the insertion is happening all time..It would reach millions of millions soon.What are precautions should be followed? -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
