Re: [PERFORM] Very slow left outer join
On Tue, 29 May 2007 17:16:57 -0700, Tyrrill, Ed [EMAIL PROTECTED] wrote: mdsdb=# explain analyze select backupobjects.record_id from backupobjects left outer join backup_location using(record_id) where backup_id = 1071; [...] Here are the two tables in the query: mdsdb=# \d backup_location Table public.backup_location Column | Type | Modifiers ---+-+--- record_id | bigint | not null backup_id | integer | not null [...] mdsdb=# \d backupobjects Table public.backupobjects Column |Type | Modifiers +-+--- record_id | bigint | not null dir_record_id | integer | name | text| extension | character varying(64) | hash | character(40) | mtime | timestamp without time zone | size | bigint | user_id| integer | group_id | integer | meta_data_hash | character(40) | Why are you using left join? The where condition is going to force the row to exist. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] performance question (something to do w/ parameterized
On Mon, 08 May 2006 19:37:37 -0400, Tom Lane [EMAIL PROTECTED] wrote: Jeffrey Tenny [EMAIL PROTECTED] writes: The server was already running with random_page_cost=2 today for all tests, because of the mods I've made to improve other problem queries in the past (my settings noted below, and before in another msg on this topic). So to nail this particular query something additional is required (even lower random_page_cost?). What's a good value for slower processors/memory and database in memory? If you're pretty sure the database will always be RAM-resident, then 1.0 is the theoretically correct value. Would it be possible to craft a set of queries on specific data that could advise a reasonable value for random_page_cost? What sort of data distribution and query type would be heavily dependant on random_page_cost? i.e. randomness of the data, size of the data compared to physical memory. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Faster db architecture for a twisted table.
On Sat, 3 Dec 2005 23:00:21 +, Rodrigo Madera [EMAIL PROTECTED] wrote: Imagine a table named Person with first_name and age. Now let's make it fancy and put a mother and father field that is a reference to the own table (Person). And to get even fuzzier, let's drop in some siblings: CREATE TABLE person( id bigint PRIMARY KEY, first_name TEXT, age INT, mother bigint REFERENCES person, father biging REFERENCES person, siblings array of bigints (don't remember the syntax, but you get the point) ); Well, this is ok, but imagine a search for brothers of person id 34. We would have to search inside the record's 'siblings' array. Is this a bad design? is this going to be slow? Do you need the array at all? alter table person add column gender; select id from person where gender = 'male' and (mother = (select mother from person where id = 34) OR father = (select father from person where id = 34)) You can change the OR depending if you want half brothers or not What would be a better design to have these kind of relationships? (where you need several references to rows inside the table we are). We use that structure (without the sibiling array) for our systems. Siblings are calculated from parents (in our case, livestock, there can be hundreds). You have to be prepared to use recursive functions and make sure that a person doesnt appear anywhere higher in their family tree. klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Query tuning help
On Sun, 8 May 2005 20:31:38 -0600, Dan Harris [EMAIL PROTECTED] wrote: Duly noted. If this method can search across rows, I'm willing to accept this overhead for the speed it would add. You could use intersect to search across rows. Using tsearch2 will look up the RED and CORVETTE using the index and intersect will pull out the commmon rows. In the meantime, is there any way I can reach my goal without Tsearch2 by just restructuring my query to narrow down the results by date first, then seq scan for the 'likes'? select distinct em.incidentid, ea.recordtext as retdata, eg.long, eg.lat from ea, em, eg, ( select ea.incidentid from ea, em where em.incidentid = ea.incidentid and em.entrydate = '2005-1-1 00:00' and em.entrydate = '2005-5-9 00:00' and recordtext like '%RED%' intersect select ea.incidentid from ea, em where em.incidentid = ea.incidentid and em.entrydate = '2005-1-1 00:00' and em.entrydate = '2005-5-9 00:00' and recordtext like '%CORVETTE%' ) as iid where em.incidentid = ea.incidentid and em.incidentid = eg.incidentid and em.entrydate = '2005-1-1 00:00' and em.entrydate = '2005-5-9 00:00' and ea.incidentid = iid.incidentid and ( recordtext like '%RED%' or recordtext like '%CORVETTE%' ) order by em.entrydate klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] which dual-CPU hardware/OS is fastest for PostgreSQL?
Anyone using power5 platform? something like an ibm eserver p5 520 running red hat linux. (http://www-1.ibm.com/servers/eserver/pseries/hardware/entry/520.html)? klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] horizontal partition
On Thu, 03 Feb 2005 02:10:15 +0100, Gaetano Mendola [EMAIL PROTECTED] wrote: why the index usage is lost if used in that way ? This is how I interpret it (if anyone wants to set me straight or improve on it feel free) Views are implemented as rules. Rules are pretty much just a macro to the query builder. When it sees the view, it replaces it with the implementation of the view. When you join a view to a table, it generates a subselect of the implementation and joins that to the other table. So the subselect will generate the entire set of data from the view before it can use the join to eliminate rows. I would like a way to make this work better as well. One of my views is 32 joins of the same table (to get tree like data for reports). klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] HELP speed up my Postgres
On Thu, 25 Nov 2004 14:00:32 +0800, JM [EMAIL PROTECTED] wrote: update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' where mobile_num in (select mobile_num from LOADED_MOBILE_NUMBERS) does loaded_mobile_numbers have a primary key or index on mobile_num? same for subscriptiontable? have you analyzed both tables? is mobile_num the same type in both tables? how does this query compare? update SUBSCRIPTIONTABLE set ACTIVEFLAG='Y' from loaded_mobile_numbers where subscriptiontable.mobile_num = LOADED_MOBILE_NUMBERS.mobile_num klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] SQL stupid query plan... terrible performance !
On Sun, 27 Jun 2004 23:29:46 -0400, Tom Lane [EMAIL PROTECTED] wrote: Jim [EMAIL PROTECTED] writes: I have one performance issue... and realy have no idea what's going on... [yawn...] Cast the constants to bigint. See previous discussions. regards, tom lane Would there be any way of adding some sort of indicator to the plan as to why sequential was chosen? eg Seq Scan on upload (type mismatch) (cost) Seq Scan on upload (statistics) (cost) Seq Scan on upload (catch-all) (cost) klint. +---+-+ : Klint Gore: Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
