Re: [PERFORM] query optimization help

Hi,   Thanks for the help. I actually got around with it by doing the following. I created a temporary table:   create table statesnew as select distinct state,state_code from postalcode where lower(country)='us';   And then changed the query to :   SELECT (SELECT sn.state FROM statesnew sn WHERE U

[PERFORM] query optimization help

Hi All,   I have the following query to generate a report grouped by "states".   SELECT distinct upper(cd.state) as mystate, SUM(d.amount) as total_amount, SUM(COALESCE(d.fee,0) + COALESCE(mp.seller_fee, 0) + COALESCE(mp.buyer_fee,0)) as total_fee FROM data d left JOIN customerdata cd ON d.uid = cd

[PERFORM] user defined data type problem while dumping?

Hi All,   I have a database running on Postgres 7.3.2. I am dumping the database schema from postgres 7.4.6 to restore it on the new Postgres version. The two postgres versions are running on different machines. I did the dump and tried restoring it. I got an error message saying type "lo" is not d

[PERFORM] slony replication

Hi All,   I installed slony1.0.5 and tried the example replication of pgbench database. That seemed to work. Now I need to replicate a DB running on a different server. slony1.0.5 is installed on the Fedora core 3 machine where Postgres 7.4.6 is installed. I have to replicate the 'test' database in

Re: [PERFORM] Postgres version change - pg_dump

Hi,   I think I miscommunicated something. I am doing a pg_dump from Postgres 7.3.2. I am restoring it on Postgres 7.4.6 on Fedora Core 3 on a different server.   I tried doing the dump and restoring it on the new DB. I did not have any problem with the UNIQUE contraint so far. But I got an error

Re: [PERFORM] Postgres version change - pg_dump

Hi,   From what I understand, I need to execute the pg_dump command from the new server( so that it will use the 7.4.6 version), but connect to the old DB. Am I right?   Thanks, SaranyaBruno Wolff III <[EMAIL PROTECTED]> wrote: On Mon, Dec 20, 2004 at 06:40:34 -0800,sarlav kumar <[EMAIL

[PERFORM] Postgres version change - pg_dump

Hi All,   Thanks to everyone for helping with my previous questions.   I have a test database running on Postgres 7.3.2.    version   - PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.96 I have another ser

[PERFORM] \d output to a file

Hi All,   I would like to write the output of the \d command on all tables in a database to an output file. There are more than 200 tables in the database. I am aware of \o command to write the output to a file. But, it will be tough to do the \d for each table manually and write the output to a fi

[PERFORM] Query Optimization

Hi all,   Can someone please help me optimize this query? Is there a better way to write this query? I am generating a report of transactions ordered by time and with details of the sender and receiver etc.   SELECT distinct a.time::date ||''||substring(a.time::time::text,1,8) as Time,CASE WHEN a.

[PERFORM] INSERT question

Hi All,   I have a question regarding multiple inserts. The following function inserts for each country found in country table, values into merchant_buyer_country.  

Re: [PERFORM] INSERT question

Thanks guys!! that worked!:) Michael Adler <[EMAIL PROTECTED]> wrote: On Mon, Dec 13, 2004 at 08:28:39AM -0800, sarlav kumar wrote:> INSERT into merchant_buyer_country (merchant_id,country,enabled,group_id) values (1203, > (SELECT code FROM country WHERE send IS NOT NULL OR receive

[PERFORM] lock problem

Hi All,Thanks for the information on replication tools!!   Now, I have a question regarding locking tables and updating tables that have a relationship to the locked table.I opened up two pgsql windows logged in using same userid.Let's say I lock a table "customerdata" on one window.begin;lock tabl

[PERFORM] pg replication tools?

Hi all,   Which is the best available PG replication tool in market now?   From searching on the internet, I found some resources on the following tools used for replication :   postgres –R  Usogres eRServer/Rserv/Dbmirror PgReplicator Mammoth PostgreSQL Replicator   Slony-I Which one of these

Re: [PERFORM] help needed -- sequential scan problem

Hi Josh,   Can you tell me in what way it affects performance? And How do I decide what value to set for the random_page_cost? Does it depend on any  other factors?   Thanks, SaranyaJosh Berkus <[EMAIL PROTECTED]> wrote: Sarlav,> I am sorry, I am not aware of what random_page_cost is, as I am new t

Re: [PERFORM] help needed -- sequential scan problem

Hi Tom,   Thanks for the help, Tom.   >The major issue seems to be in the sub-selects:> -> Seq Scan on merchant_purchase mp (cost=0.00..95.39 rows=44 width=4) (actual time=2.37..2.58 rows=6 loops=619)> Filter: (merchant_id = $0)>where the estimated row count is a factor of 7 too high. If the>estima

[PERFORM] help needed -- sequential scan problem

Hi All,   I am new to Postgres.   I have a query which does not use index scan unless I force postgres to use index scan. I dont want to force postgres, unless there is no way of optimizing this query.   The query :   select m.company_name,m.approved,cu.account_no,mbt.business_name,cda.country, (s