[GENERAL] should I reindex the table of more than 10 millions rows regularly when more data are inserted?

Hi everyone, I have a big table (more than 10 millions rows) and create an index (three columns) ( the index is not created with creating the table). Within one day of the index created, the performance of query searching is ok. But with more than several thousands rows of data inserted into

Re: [GENERAL] An issue with max() and order by ... limit 1 in postgresql8.3-beta3

sc.channel=max01.r_channel and sc.external_ins=ec.measure_name and sc.remarks='累积值' and ec.flowmeter_caliber='流量' ) acc_data where acc_data.r_sloc = rt_data.r_sloc order by r_max01_sloc desc ###

[GENERAL] An issue with max() and order by ... limit 1 in postgresql8.3-beta3

Hi everyone, I am using postgresql 8.3-beta3. I have a table 'test' with three fields: sid data date 11.1 2009-09-01 1:00:00 12.1 2010-01-01 1:00:20 23.1 2009-09-01 1:00:10

[GENERAL] two field table: field1, field2, how to add a third one with the auto-generated value replace(field2,'channel','AAAA')?

Hi evreyone, I have a table with two fields field1field2 1 1channel 2 2channel 3 3channel Now I want to add the third one with the value replace(field2,'channel','') like field1field2 field3 1 1channelreplace(fie

[GENERAL] How to save the results of replace(split_part(trim(both ' ' from "vx6000__12channel"),'__',2),'channel','myChannel') in my query into a temp variable?

Hi everyone, I have a big query (see below attached) in which all where clauses have sc.channel = replace(split_part(trim(both ' ' from ec.instantance_flux),'__',2),'channel','myChannel'). The value of ec.instantance_flux is like the format: "vx6000__12channel". I want to replace "channel" wit

[GENERAL] Three fields table: id-data-date_time, how to get max() and date_time same time?

Hi everyone, I have a table "test" which has three fields: id data date_time 12 2009-10-1 12:12:12 110 2009-10-1 12:22:10 23 2009-10-1 12:10:32 21 2009-10-1 12:30:32 with the sql: select max(data), id from test w

Re: [GENERAL] Very slow searching in a table with more than 10 millions recovered records from a backup file...

Any suggestions. ouyang 2009/6/12 Grzegorz Jaśkiewicz > On Fri, Jun 12, 2009 at 9:56 AM, zxo102 ouyang wrote: > > Hi there, > > I have an application with a database (pgsql) which has a big table > (> > > 10 millions records) in windows 2003. Some times, I need to instal

[GENERAL] Very slow searching in a table with more than 10 millions recovered records from a backup file...

Hi there, I have an application with a database (pgsql) which has a big table (> 10 millions records) in windows 2003. Some times, I need to install the new version of the application. Here is what I did: 1. back up the big table via pgadmin III, 2. stop the pgsql in the old version of the app

Re: [GENERAL] How to speed up the first-time-searching in pgsql?

Hi there, Thanks for your suggestions. I do have an application running on the machine all the time. In fact, the application keeps writing real-time monitoring data into the database. Based on my understanding of your messages, I can't do anything to speed up the first-time-searching. Probably I c

[GENERAL] How to speed up the first-time-searching in pgsql?

Hi all, I have a table which has more than 10millions records in pgsql which is running on window 2003. During night, nobody search the database. In morning, when people start to the application, it will take more than 30 seconds to get database back. After several times of same searching, the

[GENERAL] how to "group" several records with same timestamp into one line?

Hi everyone, My data with same timestamp "2008-11-12 12:12:12" in postgresql are as follows rowid data unitchannel create_on -- 11.5 MPa channel1 2008-11-12 12:12:12 2

[GENERAL] how to several records with same timestamp into one line?

Hi everyone, My data with same timestamp "2008-11-12 12:12:12" in postgresql are as follows rowid data unitchannel create_on -- 11.5 MPa channel1 2008-11-12 12:12:12 2