Re: [PERFORM] speeding up a select with C function?
Hi, On 08.03.2004, at 02:29, Christopher Kings-Lynne wrote: explain analyze select ((t0.int_value-t1.int_value)*(t0.int_value-t1.int_value)) from job_property t0, job_property t1 where t0.id_job_profile = 5 and t1.id_job_profile = 6 and t1.id_job_attribute = t0.id_job_attribute and t1.int_value t0.int_value; Don't bother with C function, use SQL function instead. You could get a 50% speedup. Is this always the case when using SQL instead of the C API to get values or only the function 'call' itself? We are thinking to use C functions which are optimized for the G5 altivec unit. regards David ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] speeding up a select with C function?
Don't bother with C function, use SQL function instead. You could get a 50% speedup. Is this always the case when using SQL instead of the C API to get values or only the function 'call' itself? We are thinking to use C functions which are optimized for the G5 altivec unit. SQL functions are stored prepared, so there is less per-call query planning overhead. I'm not sure there'd be much advantage to doing them in C... Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] speeding up a select with C function?
David Teran [EMAIL PROTECTED] writes: Merge Join (cost=5369.08..5383.14 rows=150 width=4) (actual time=2.527..2.874 rows=43 loops=1) Merge Cond: (outer.id_job_attribute = inner.id_job_attribute) Join Filter: (inner.int_value outer.int_value) - Sort (cost=2684.54..2686.37 rows=734 width=6) (actual time=1.140..1.177 rows=232 loops=1) Sort Key: t0.id_job_attribute - Index Scan using job_property_short__id_job_profile__fk_index on job_property_short t0 (cost=0.00..2649.60 rows=734 width=6) (actual time=0.039..0.820 rows=232 loops=1) Index Cond: (id_job_profile = 5) - Sort (cost=2684.54..2686.37 rows=734 width=6) (actual time=1.175..1.223 rows=254 loops=1) Sort Key: t1.id_job_attribute - Index Scan using job_property_short__id_job_profile__fk_index on job_property_short t1 (cost=0.00..2649.60 rows=734 width=6) (actual time=0.023..0.878 rows=254 loops=1) Index Cond: (id_job_profile = 6) Total runtime: 3.065 ms (12 rows) So the question is how to tell Postgres to use the index. Er, which part of that do you think is not using an index? More generally, it is not necessarily the case that a join *should* use an index. I'm a bit surprised that the above bothers to sort; I'd expect a hash join to be more appropriate. Have you tried experimenting with enable_mergejoin and the other planner-testing settings? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[PERFORM] speeding up a select with C function?
Hi, we need to optimize / speed up a simple select: explain analyze select ((t0.int_value-t1.int_value)*(t0.int_value-t1.int_value)) from job_property t0, job_property t1 where t0.id_job_profile = 5 and t1.id_job_profile = 6 and t1.id_job_attribute = t0.id_job_attribute and t1.int_value t0.int_value; the result from explain analyze is: first run: -- Merge Join (cost=8314.36..8336.21 rows=258 width=8) (actual time=226.544..226.890 rows=43 loops=1) Merge Cond: (outer.id_job_attribute = inner.id_job_attribute) Join Filter: (inner.int_value outer.int_value) - Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual time=113.781..113.826 rows=232 loops=1) Sort Key: t0.id_job_attribute - Index Scan using job_property__id_job_profile__fk_index on job_property t0 (cost=0.00..4105.87 rows=1026 width=8) (actual time=0.045..113.244 rows=232 loops=1) Index Cond: (id_job_profile = 5) - Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual time=112.504..112.544 rows=254 loops=1) Sort Key: t1.id_job_attribute - Index Scan using job_property__id_job_profile__fk_index on job_property t1 (cost=0.00..4105.87 rows=1026 width=8) (actual time=0.067..112.090 rows=254 loops=1) Index Cond: (id_job_profile = 6) Total runtime: 227.120 ms (12 rows) second run: Merge Join (cost=8314.36..8336.21 rows=258 width=8) (actual time=4.323..4.686 rows=43 loops=1) Merge Cond: (outer.id_job_attribute = inner.id_job_attribute) Join Filter: (inner.int_value outer.int_value) - Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual time=2.666..2.700 rows=232 loops=1) Sort Key: t0.id_job_attribute - Index Scan using job_property__id_job_profile__fk_index on job_property t0 (cost=0.00..4105.87 rows=1026 width=8) (actual time=0.279..2.354 rows=232 loops=1) Index Cond: (id_job_profile = 5) - Sort (cost=4157.18..4159.75 rows=1026 width=8) (actual time=1.440..1.477 rows=254 loops=1) Sort Key: t1.id_job_attribute - Index Scan using job_property__id_job_profile__fk_index on job_property t1 (cost=0.00..4105.87 rows=1026 width=8) (actual time=0.040..1.133 rows=254 loops=1) Index Cond: (id_job_profile = 6) Total runtime: 4.892 ms (12 rows) I have run vacuum analyze before executing the statements. I wonder now if there is any chance to speed this up. Could we use a C function to access the indexes faster or is there any other chance to speed this up? The Server is a dual G5/2GHZ with 8 GB of RAM and a 3.5 TB fiberchannel RAID. The job_property table is about 1 GB large (checked with dbsize) and has about 6.800.000 rows. regards David ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] speeding up a select with C function?
explain analyze select ((t0.int_value-t1.int_value)*(t0.int_value-t1.int_value)) from job_property t0, job_property t1 where t0.id_job_profile = 5 and t1.id_job_profile = 6 and t1.id_job_attribute = t0.id_job_attribute and t1.int_value t0.int_value; Don't bother with C function, use SQL function instead. You could get a 50% speedup. Chris ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
