Re: [SQL] Alter Table/Indexing
At 02:20 AM 3/25/2009, pgsql-sql-ow...@postgresql.org wrote: To: Zdravko Balorda cc: pgsql-sql@postgresql.org Subject: Re: Alter Table/Indexing In-reply-to: <49c89fea.8060...@siix.com> References: <49c89fea.8060...@siix.com> Comments: In-reply-to Zdravko Balorda message dated "Tue, 24 Mar 2009 09:55:06 +0100" Date: Tue, 24 Mar 2009 10:35:31 -0400 Message-ID: <27189.1237905...@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200903/84 X-Sequence-Number: 32327 Zdravko Balorda writes: > I wonder does ATER TABLE TYPE, SET, depends on indexes, like INSERT does > in a sense it may be faster to drop and recreate index than sorting > after every row inserted. ALTER TABLE TYPE already rebuilds the indexes; you won't make the overall process any faster by doing that by hand. regards, tom lane I had a case (a long time ago) where I was on MS SQL in a production environment. We had a number of indices which were system related - meaning they were used infrequently to speed up certain administrative functions. When doing a bulk load we found that if we dropped these indices (but kept the ones that were crucial for production) we could significantly speed up the "effective downtime" of the system b/c any DDL statement was executed faster. We would then schedule these indices to be re-created at later dates, spreading out the load (b/c the system was in production at that point). I wonder if Postgres functions similarly for such a use case? As Tom says, the total processing time is fixed: you have to upload the data and rebuild all the indices, but if there are non-critical indices, you can go from "zero" to "data loaded" faster by dropping them and rebuilding them manually later? Thanks for any insight on that (and I hope my question helps the OP as well - if this seems off topic let me know), Steve -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Special sort querstion
Hello everyone,
the following query
SELECT id, start_lat, start_lng, end_lat, end_lng
FROM strecken
WHERE ST_Intersects(strecke, geomfromtext('POLYGON((
52.5204252 13.3169317,
52.5407887 13.3169317,
52.5407887 13.3534097,
52.5204252 13.3534097,
52.5204252 13.3169317
))')) ORDER BY id;
produces by coincidence the following desired result:
idstart_latstart_lngend_latend_lng
18074 52.5212087 13.318111952.5226187 13.3183479
18075 52.5226187 13.318347952.5237154 13.3189702
18076 52.5237154 13.318970252.5244204 13.3201289
18077 52.5244204 13.320128952.5248120 13.3217812
18095 52.5195701 13.338185552.5204710 13.3390224
18096 52.5204710 13.339022452.5213653 13.3393443
18097 52.5213653 13.339344352.5219529 13.3395588
18098 52.5219529 13.339558852.5223511 13.3400846
20293 52.5408279 13.333883352.5404625 13.3350205
20294 52.5404625 13.335020552.5395881 13.3385825
20295 52.5395881 13.338582552.5387398 13.3447623
Its desired because every single row end_lat and end_lng has the same
value as the next row's start_lat and start_lng field (except last one).
I would like to achieve the same result without using id column inside
"order by" clause. Is that possible? How would that query look like?
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Can we load all database objects in memory?
Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak
Re: [SQL] Can we load all database objects in memory?
Deepak, please don't cross-post the same question to 3 different lists. The short answer is no, you cannot force PostgreSQL to load all objects into memory. However when you proper configure PostgreSQL most, if not all of your data will be cached by the OS and/or PostgreSQL shared memory system. Ries On Mar 25, 2009, at 2:20 PM, DM wrote: Hi All, I have a database of 10GB. My Database Server has a RAM of 16GB Is there a way that I can load all the database objects to memory? Thanks for your time and taking a look at this question. Thanks Deepak -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Table to Excel
Hi , Does any one how to convert table to excel. I just tried the COPY table_name to '/home/user/output.xls' using delimiters ','; but the out put of the table that is the column in a table are tab separted, all were merged in a single cell. -Nicholas I
