[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] Using bigint needs explicit cast to use the index
Steven Butler wrote: I've recently converted a database to use bigint for the indices. Suddenly simple queries like select * from new_test_result where parent_id = 2 are doing full table scans instead of using the index. This is fixed in CVS HEAD. In the mean time, you can enclose the integer literal in single quotes, or explicitely cast it to the type of the column. FWIW, this is an FAQ. -Neil ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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])
[PERFORM] simple query join
Title: Message
Hi
all,
I've got what
should be a relatively simple join between two tables that is taking forever and
I can't work out why.
Version
7.3.4RH.
It can't be
upgraded because the system is kept in sync with RedHat Enterprise (using
up2date). Not my system otherwise I'd do that :(
Database has been
'vacuum analyze'd.
blah= \d
sq_asset;
Table "public.sq_asset"
Column
|
Type
|
Modifiers
+-+--type_code
| character varying(100) | not
nullversion | character
varying(20) | not null default
'0.0.0'name
| character varying(255) | not null default
''short_name | character
varying(255) | not null default
''status |
integer
| not null default 1languages |
character varying(50) | not null default
''charset | character
varying(50) | not null default
''force_secure |
character(1)
| not null default
'0'created | timestamp
without time zone | not
nullupdated | timestamp
without time zone | not nullcreated_userid | character
varying(255) | not null default
'0'updated_userid | character
varying(255) | not null default
'0'assetid |
integer
| not null default 0Indexes: sq_asset_pkey primary key btree
(assetid)
blah= select
count(*) from sq_asset;count ---16467(1
row)
blah= \d
sq_asset_permission;
Table "public.sq_asset_permission" Column
|
Type
| Modifiers
++--permission
|
integer
| not null default 0access |
character(1) | not
null default '0'assetid | character varying(255) |
not null default '0'userid | character
varying(255) | not null default '0'Indexes: sq_asset_permission_pkey primary
key btree (assetid, userid, permission)
"sq_asset_permission_access" btree ("access")
"sq_asset_permission_assetid" btree (assetid)
"sq_asset_permission_permission" btree (permission)
"sq_asset_permission_userid" btree (userid)
blah= select
count(*) from sq_asset_permission;count
---73715(1 row)
EXPLAIN ANALYZE
SELECT p.*FROM sq_asset a, sq_asset_permission pWHERE a.assetid =
p.assetidAND p.permission = '1'AND p.access = '1'AND p.userid =
'0';
QUERY
PLANNested
Loop (cost=0.00..4743553.10 rows=2582 width=27) (actual
time=237.91..759310.60 rows=11393 loops=1) Join Filter:
(("inner".assetid)::text = ("outer".assetid)::text) -
Seq Scan on sq_asset_permission p (cost=0.00..1852.01 rows=2288 width=23)
(actual time=0.06..196.90 rows=12873
loops=1) Filter:
((permission = 1) AND ("access" = '1'::bpchar) AND (userid = '0'::character
varying)) - Seq Scan on sq_asset a
(cost=0.00..1825.67 rows=16467 width=4) (actual time=1.40..29.09 rows=16467
loops=12873)Total runtime: 759331.85 msec(6
rows)
It's a straight
join so I can't see why it would be this slow.. The tables are pretty small
too.
Thanks for any
suggestions :)
Chris.
Re: [PERFORM] simple query join
On Mon, 8 Mar 2004, Chris Smith wrote: assetid| integer | not null default 0 assetid| character varying(255) | not null default '0' The types above does not match, and these are the attributes you use to join. -- /Dennis Björklund ---(end of broadcast)--- TIP 8: explain analyze is your friend
