Re: [ADMIN] Explain Analyze help
Mark Lubratt wrote: Obivously, I have a lot of sequential scans going on, at about what cost does it start to make sense to make an index? Or, should I just put in a bunch of indexes and let the optimizer decide whether or not to use them? Read the chapters on Indexes and Performance Tips in the documentation. They contain various example scenarios where indexes are useful. Keep in mind that indexes are useful only if your table is large (say, 1 rows or more) and the fraction you select is small (say, 1%). The costs that EXPLAIN prints out are not meaningful absolute values; they are only useful to compare two plans of the same query. So in absence of a better approach, your job is to 1. EXPLAIN ANALYZE without index 2. create index 3. EXPLAIN ANALYZE with index (And don't forget to run ANALYZE sometime.) If there is a significant improvement, then keep the index. Remember, too many indexes slow down updates. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[ADMIN] started Data Warehousing
Hello people! I have a question, I am going to begin a project for the University in the area of Data Warehousing and I want to use postgres. Do you have some recommendation to me? Thanks!! Greetings, Pablo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Multiple postmasters for one data directory...
Hi all, I've got a situation on certain machines where approximately 70 postmasters are running. Each with one database, and one data directory. This is fine most of the time, except for a certain time of the month, when a very large consolidation process has to run on each database. At this time it is impossible to have all databases started with more shared memory, and I basically lose the benefits of using a cluster that can make use of much more memory at a time. I've managed to make such a database cluster on a separate postmaster, and re-linked all the database directories to point to the data directories of all the other postmasters. Although this workaround seems to be working rather well, I know for one thing that it could be disastrous to have two postmasters running on the same data directory at the same time, so I'll ensure that that doesn't happen. Is what I'm doing VERY bad, and are there more things I'm overlooking? Can this setup possibly break everything? Kind Regards Stefan pgp0.pgp Description: PGP signature
Re: [ADMIN] History Tables Vs History Field
I have a table that will recieve about 2000 inserts per day, Although it will technically never be dead data, about 99.999% of it will be uninteresing after 30-40 days, My problem is that I dont know if I should create a new table that is a history table or add a indexed field and ignore the data in queries unless someone asks for it. The latter is my prefered way of dealing with it, Is there something Im missing? Is there another way to do this? Any Suggestions are appreciated. Hello Bart, We normally do this sort of thing by using a history table. There's no strong reason why we do it this way as such, except that it just seems a logical separation. Advantages are that the data (in the history table) is easier to manipulate (e.g., archive) without disturbing users who are looking at the current 'live' data. Also, it's simple to query both live data old history data by using a union should we need to. Normally, we setup a db rule that on deletion of a record from the live table, copies it into the history table. In this way, we don't have to manage it at the application level. In the app., we simply delete the record when we're done with it the db takes care of copying it into the history table for us. We use a rule like this: -- This is an SQL script to define a rule for deletions on buffer_pallets. -- It automatically copies the records in the buffer_pallets table into the -- pallet_history table \connect - postgres drop rule del_buffer_pallets; create rule del_buffer_pallets as on delete to buffer_pallets do insert into pallet_history select * from buffer_pallets where pallet_urn = OLD.pallet_urn; It's simple clean. You can setup a cron job to clear out data older than n days etc. Hope this helps. Deep Joy - Paul -- Paul M. Breen, Software Engineer - Computer Park Ltd. Tel: (01536) 417155 Email: [EMAIL PROTECTED] - This private and confidential e-mail has been sent to you by Computer Park Ltd. If you are not the intended recipient of this e-mail and have received it in error, please notify us via the email address or telephone number below, and then delete it from your mailbox. Email: [EMAIL PROTECTED] Tel: +44 (0) 1536 417155 Fax: +44 (0) 1536 417566 Head Office: Computer Park Ltd, Broughton Grange, Headlands, Kettering Northamptonshire NN15 6XA Registered in England: 3022961. Registered Office: 6 North Street, Oundle, Peterborough PE8 4AL = ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] Multiple postmasters for one data directory...
Stef [EMAIL PROTECTED] writes: I've managed to make such a database cluster on a separate postmaster, and re-linked all the database directories to point to the data directories of all the other postmasters. Although this workaround seems to be working rather well, I know for one thing that it could be disastrous to have two postmasters running on the same data directory at the same time, so I'll ensure that that doesn't happen. Is what I'm doing VERY bad, and are there more things I'm overlooking? Can this setup possibly break everything? I'm afraid you have already shot yourself in the foot. The above cannot work because there is no way to share pg_xlog or pg_clog across database clusters. You have now got transaction numbers from the shared postmaster inserted into the other databases, and when you go back to separate postmasters you will have corruption. I'm surprised you do not already see inconsistencies, because if the shared installation was a fresh one then it would probably see most of the committed transaction numbers of the other databases as being in the future. Get out your backup tapes, because what you have on disk now is just a pile of inconsistent bits. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[ADMIN] IMPORT or ETL tools
Hi All, Do anyone of you aware of any good PG import tool same as SQL*LOADER in ORACLE We are doing some DataWarehouse work and need to import a large set of data from csv files. We are trying a import tool from ems-hitech.com, but I have troubles when loading time and date columns. Any help is much appreciated. Thanks Louie -- | Creating NNM_HOST Table -- | CREATE TABLE NNM_NODES ( lastLoadTime_e DATE, domain_name VARCHAR(10), ovtopo_idINTEGER NOT NULL, node_typeVARCHAR(10), node_nameVARCHAR(30), ov_statusVARCHAR(10), ipaddressVARCHAR(15) ); data.csv 31/01/2004,D1H01,845,IP,dfrQCQCRD1,Normal,142.130.35.1 31/01/2004,D1H01,849,IP,dfrQCIMCP1,Normal,142.130.130.33 31/01/2004,D1H01,853,IP,dfrQCSIBL1,Normal,142.130.130.19 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] IMPORT or ETL tools
Louie Kwan wrote: We are trying a import tool from ems-hitech.com, but I have troubles when loading time and date columns. ... data.csv 31/01/2004,D1H01,845,IP,dfrQCQCRD1,Normal,142.130.35.1 31/01/2004,D1H01,849,IP,dfrQCIMCP1,Normal,142.130.130.33 31/01/2004,D1H01,853,IP,dfrQCSIBL1,Normal,142.130.130.19 Before doing your COPY (I'm assuming you're doing a COPY) have you done a set datestyle to 'European';? -- Greg Spiegelberg Sr. Product Development Engineer Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Cranel. Technology. Integrity. Focus. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Multiple postmasters for one data directory...
Tom Lane mentioned : = Get out your backup tapes, because what you have on disk now is just a = pile of inconsistent bits. I haven't actually done anything but SELECT's from the shared postmaster, and it was a test machine. Phew!! Glad I asked first... I think the way I'll do it, is by using initlocation, as this will solve all my problems, except for having to reload all databases. Thanks for the enlightening answer! Kind Regards Stefan pgp0.pgp Description: PGP signature
