Re: [PERFORM] Fwd: Index on table when using DESC clause

2005-05-23 Thread Andrew Lazarus
As far as I know, to use a straight index Postgres requires either ORDER BY pages, description -- or -- ORDER BY pages DESC, description DESC. If you want the results by pages DESC, description ASC, then you have to make an index on an expression or define your own operator or something esoter

[PERFORM] What *_mem to increase when running CLUSTER

2005-08-25 Thread Andrew Lazarus
Should I temporarily increase sort_mem, vacuum_mem, neither, or both when doing a CLUSTER on a large (100 million row) table where as many as half of the tuples are deadwood from UPDATEs or DELETEs? I have large batch (10 million row) inserts, updates, and deletes so I'm not sure frequent vacuu

Re: [PERFORM] group by will not use an index?

2007-01-09 Thread Andrew Lazarus
For the reasons indicated (that is, MVCC), PG can not do a DISTINCT or the equivalent GROUP BY from index values alone. If this table is large, perhaps you could denormalize and maintain a summary table with date (using truncation) and count, updated with triggers on the original table. This tabl

Re: [PERFORM] Subselect query enhancement

2007-02-01 Thread Andrew Lazarus
>> How about this option: >> >> SELECT distinct ip_info.* FROM ip_info RIGHT JOIN network_events USING >> (ip) RIGHT JOIN host_events USING (ip) WHERE >> (network_events.name='blah' OR host_events.name = 'blah') AND >> ip_info.ip IS NOT NULL; MA> Nah, that seems to be much much worse. The othe

[PERFORM] index structure for 114-dimension vector

2007-04-20 Thread Andrew Lazarus
t of issue? Thanks. Andrew Lazarus [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq

Re: [PERFORM] index structure for 114-dimension vector

2007-04-20 Thread Andrew Lazarus
rs, when I killed it, and I wasn't in retrospect sure an index that took something like 6GB by itself would be helpful on a 2GB of RAM box. MK> I don't think that will work for the vector norm i.e: MK> |x - y| = sqrt(sum over j ((x[j] - y[j])^2)) MK> Cheers MK> Mark

Re: [PERFORM] index structure for 114-dimension vector

2007-05-01 Thread Andrew Lazarus
Let me just thank the list, especially for the references. (I found similar papers myself with Google: and to think I have a university library alumni card and barely need it any more!) I'll write again on the sorts of results I get. BEGIN:VCARD VERSION:2.1 N:Lazarus;Andrew;;;Ph.D. FN:Andrew Lazar

Re: [PERFORM] Please Help: PostgreSQL performance Optimization

2006-01-12 Thread Andrew Lazarus
Jamal Ghaffour wrote: CREATE TABLE cookies ( domain varchar(50) NOT NULL, path varchar(50) NOT NULL, name varchar(50) NOT NULL, principalid varchar(50) NOT NULL, host text NOT NULL, value text NOT NULL, secure bool NOT NULL, timestamp timestamp with time zone NOT NULL DEF

Re: [PERFORM] execution plan : Oracle vs PostgreSQL

2006-02-06 Thread Andrew Lazarus
Indeed there is: you can use an ARRAY constructor with SELECT. Here's some PGPLSQL code I have (simplified and with the variable names shrouded). SELECT INTO m ARRAY(SELECT d FROM hp WHERE hp.ss=$1 ORDER BY 1); FERREIRA, William (VALTECH) wrote: maybe t

Re: [PERFORM] Query Optimization

2004-12-15 Thread Andrew Lazarus
sarlav kumar wrote: 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)

[PERFORM] when is a DELETE FK trigger planned?

2008-02-25 Thread Andrew Lazarus
I have a cascading delete trigger that is obviously using a seqscan. (Explain analyze shows that trigger as taking over 1000s while all other triggers are <1s. The value in test delete didn't even appear in this child table, so an index scan would have been almost instant.) If I do DELETE FROM chi

Re: [PERFORM] when is a DELETE FK trigger planned?

2008-02-27 Thread Andrew Lazarus
route. What I've done is remove the FK (maybe it would be better to leave it albeit disabled for documentation) and written my own AFTER DELETE trigger that uses EXECUTE to delay planning until the actual value is known. This appears to work correctly. -- Sincerely, Andrew Lazarusm

Re: [PERFORM] Deleting millions of rows

2009-02-03 Thread Andrew Lazarus
TO bigtable SELECT * FROM remnant; COMMIT; ANALYSE bigtable; works well because there is no possibility of my forgetting FKs. -- Sincerely, Andrew Lazarusmailto:and...@pillette.comBEGIN:VCARD VERSION:2.1 N:Lazarus;Andrew;;;Ph.D. FN:Andrew Lazarus, Ph.D. EMAIL;PREF;INTERNET:and...@p

[PERFORM] JOIN order, 15K, 15K, 7MM rows

2004-02-20 Thread Andrew Lazarus
All three tables have the same integer key, and it's indexed.   I parenthesized the joins to do the two small tables first.   I'm running and INSERT INTO ... SELECT query with this join (one record added per record in join), 4 hours down and all I have to show for it is 100 recycled transact