Re: [PERFORM] Many to many join seems slow?

2007-05-15 Thread Drew Wilson
ngas wrote: Drew Wilson wrote: Merge Join (cost=524224.49..732216.29 rows=92447 width=97) (actual time=1088.871..1351.840 rows=170759 loops=1) ... Total runtime: 1366.757 ms It looks like the query actual runs in less than 3 seconds, but it takes some time to fetch 170759 rows to the client. -

Re: [PERFORM] Many to many join seems slow?

2007-05-15 Thread Drew Wilson
Yes, I'll be filtering by string value. However, I just wanted to see how long it takes to scan all translations in a particular language. Drew On May 15, 2007, at 9:00 AM, Daniel Cristian Cruz wrote: 2007/5/15, Drew Wilson <[EMAIL PROTECTED]>: =# explain SELECT s.source_id,

Re: [PERFORM] Many to many join seems slow?

2007-05-15 Thread Drew Wilson
ime=0.086..110.564 rows=186176 loops=1) Total runtime: 1366.757 ms (16 rows) On May 15, 2007, at 7:05 AM, Alvaro Herrera wrote: Drew Wilson escribió: =# explain SELECT s.source_id, s.value AS sourceValue, t.value AS translationValue FROM source s, translation_pair tp,

[PERFORM] Many to many join seems slow?

2007-05-15 Thread Drew Wilson
I'm trying to debug a query that gets all the French translations for all US string values. Ultimately, my goal is to rank them all by edit distance, and only pick the top N. However, I cannot get the basic many-to-many join to return all the results in less than 3 seconds, which seems slow

Re: [PERFORM] how to efficiently update tuple in many-to-many relationship?

2007-04-10 Thread Drew Wilson
On Apr 10, 2007, at 6:54 AM, Tom Lane wrote: Drew Wilson <[EMAIL PROTECTED]> writes: The SELECT is not slow, so its a side effect of the update... Looking at the table definition, there is a "BEFORE ON DELETE" trigger defined, two CHECK constraints for this table, and th

Re: [PERFORM] how to efficiently update tuple in many-to-many relationship?

2007-04-09 Thread Drew Wilson
Thanks for the suggestions, Tom. But I'm still stumped. On Apr 9, 2007, at 7:13 PM, Tom Lane wrote: Drew Wilson <[EMAIL PROTECTED]> writes: Here's the query plan for a SELECT statement that returns 1,207,161 rows in 6 seconds. ... And here's the query plan for the UPDAT

Re: [PERFORM] how to efficiently update tuple in many-to-many relationship?

2007-04-09 Thread Drew Wilson
rue) Filter: is_public -> Hash (cost=31861.82..31861.82 rows=1690282 width=4) -> Seq Scan on translation_pair_data (cost=0.00..31861.82 rows=1690282 width=4) I figure I must be doing something wrong here. Thanks for the he

[PERFORM] how to efficiently update tuple in many-to-many relationship?

2007-04-09 Thread Drew Wilson
I have 2 tables (A,B) joined in a many-to-many relationship via a join table ("membership"), where updating table A based on table B takes a very long time. Tables A and B have oid primary keys (a_id and b_id respectively). The join table, "membership", has its own oid primary key (membersh

[PERFORM] profiling PL/pgSQL?

2006-11-03 Thread Drew Wilson
I have 700 lines of non-performant pgSQL code that I'd like to profile to see what's going on. What's the best way to profile stored procedures? Thanks, Drew ---(end of broadcast)--- TIP 4: Have you searched our list archives? ht