[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

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

by r_max01_sloc desc ## 2010/1/10 Scott Marlowe scott.marl...@gmail.com On Sat, Jan 9, 2010 at 2:46 PM, Andreas Kretschmer akretsch...@spamfence.net wrote: Stefan Kaltenbrunner ste...@kaltenbrunner.cc wrote: Andreas Kretschmer wrote: zxo102 ouyang zxo

[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] 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 with

[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 1channel

[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

[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

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

Grzegorz, Thank you very much. I will do that. I have another question: if I do the following steps, does it hurt pgsql? step 1. stop the pgsql in the old version of the application; the whole application is installed in c:/xbop and pgsql is located in c:/xbop/pgsql; step 2. rename c:/xbop to

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

[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