Re: [PERFORM] Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)
Robert Bernabe wrote: I'm happy (actually ecstatic) to report that Win2kPro + PG performance is slightly faster than Win2kPro + MSSQL/MSDE. Linux(FC7) + PG 8.x performance seems to be 3x faster than Win2KPro + MSSQL/MSDE for our stored functions. Thanks for all the help! Am a believer now. :) That's great news Robert - thanks for sharing! Regards, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Evaluation of PG performance vs MSDE/MSSQL 2000 (not 2005)
Robert Bernabe wrote: In a nutshell it seems that MS SQL allows bad T-SQL code by optimizing and ignoring redundant/useless from and where clauses in an update statement whereas plpgsql will execute exactly what the code is asking it to do... We had several update instances in the T-SQL code that looked like this : update _tbl_tmp2 set LongBackPeriod = (select count (EPeriod) from _tbl_tmp1 where _tbl_tmp1.Row = _tbl_tmp2.Row); -- from _tbl_tmp2 tmp2, _tbl_tmp1 tmp1 where tmp2.Row = tmp1.Row; --- Those lines are not totally useless from DB point of view. If there is no rows that match the join, the WHERE clause will be false, and no rows will be updated. So I'm sure MS SQL doesn't ignore those lines, but does use a more clever plan. Perhaps it stops processing the join as soon as is finds a match, while we perform the whole join, for example. In T-SQL, the performance is the same whether the last two lines are there or not... In plpgsql, this is not the case the from and where clauses are not necessary and probably creates an internal (rather useless and time consuming) inner join in plpgsql which accounts for the original performance issue. You can check the access plan with EXPLAIN. I'm happy (actually ecstatic) to report that Win2kPro + PG performance is slightly faster than Win2kPro + MSSQL/MSDE. Linux(FC7) + PG 8.x performance seems to be 3x faster than Win2KPro + MSSQL/MSDE for our stored functions. Thanks for all the help! Am a believer now. :) Nice to hear :). -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
If we are talking about enhancement requests, I would propose we create a role that can be granted/revoked that enables a user to see dictionary objects like source code. Secondly, users should be able to see their own code they write but not others unless they have been granted this dictionary role. Revoking pg_proc isn't good for users that shouldn't see other's code but still need to be able to see their own code. Jon -Original Message- From: Kris Jurka [mailto:[EMAIL PROTECTED] Sent: Monday, December 17, 2007 10:51 PM To: Merlin Moncure Cc: Roberts, Jon; Jonah H. Harris; Bill Moran; Joshua D. Drake; pgsql- [EMAIL PROTECTED] Subject: Re: [PERFORM] viewing source code On Mon, 17 Dec 2007, Merlin Moncure wrote: the table is pg_proc. you have to revoke select rights from public and the user of interest. be aware this will make it very difficult for that user to do certain things in psql and (especially) pgadmin. it works. a better solution to this problem is to make a language wrapper for pl/pgsql that encrypts the source on disk. afaik, no one is working on th is. it would secure the code from remote users but not necessarily from people logged in to the server. the pg_proc hack works ok though. Another enhancement that would improve this situation would be to implement per column permissions as the sql spec has, so that you could revoke select on just the prosrc column and allow clients to retrieve the metadata they need. Kris Jurka ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [PERFORM] viewing source code
Roberts, Jon escribió: Revoking pg_proc isn't good for users that shouldn't see other's code but still need to be able to see their own code. So create a view on top of pg_proc restricted by current role, and grant select on that to users. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
So you are saying I need to create a view per user to achieve this? That isn't practical for an enterprise level database. I'm basically suggesting row level security that would be implemented for a system table and then RLS could be used for user defined tables too. Jon -Original Message- From: Alvaro Herrera [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 18, 2007 12:27 PM To: Roberts, Jon Cc: 'Kris Jurka'; Merlin Moncure; Jonah H. Harris; Bill Moran; Joshua D. Drake; pgsql-performance@postgresql.org Subject: Re: [PERFORM] viewing source code Roberts, Jon escribió: Revoking pg_proc isn't good for users that shouldn't see other's code but still need to be able to see their own code. So create a view on top of pg_proc restricted by current role, and grant select on that to users. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] viewing source code
Roberts, Jon wrote: So you are saying I need to create a view per user to achieve this? That isn't practical for an enterprise level database. Surely you'd just have: CREATE VIEW ... AS SELECT * FROM pg_proc WHERE author=CURRENT_USER -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: 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] viewing source code
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 18 Dec 2007 10:05:46 -0600 Roberts, Jon [EMAIL PROTECTED] wrote: If we are talking about enhancement requests, I would propose we create a role that can be granted/revoked that enables a user to see dictionary objects like source code. Secondly, users should be able to see their own code they write but not others unless they have been granted this dictionary role. You are likely not going to get any support on an obfuscation front. This is an Open Source project :P Sincerely, Joshua D. Drake - -- The PostgreSQL Company: Since 1997, http://www.commandprompt.com/ Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate SELECT 'Training', 'Consulting' FROM vendor WHERE name = 'CMD' -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFHaBcBATb/zqfZUUQRAiHPAJ9qXeWnMEKRItO6HKZpqi/c4r5XdQCeMC4M Ivdv24nAt63YkJz/5mr95aQ= =+3Wm -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] viewing source code
Roberts, Jon escribió: So you are saying I need to create a view per user to achieve this? That isn't practical for an enterprise level database. No -- that would be quite impractical indeed. I'm talking about something like revoke all privileges on pg_proc from public; create view limited_pg_proc as select * from pg_proc where proowner = (select oid from pg_authid where rolname = current_user); grant select on limited_pg_proc to public; Of course, it is only a rough sketch. It needs to be improved in a number of ways. But it shows that even with pure SQL the solution is not far; with backend changes it is certainly doable (for example invent a separate view source privilege for functions). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[PERFORM] Optimising a query
I have a query that looks like this: SELECT DISTINCT ON (EEFSCode) eefsbase.company||eefsbase.department||eefsbase.branch||eefsbase.franchise||eefsbase.subledger||eefsbase.account AS EEFSCode, eefsbase.company AS company_code, eefsbase.branch AS branch_code, eefsbase.department AS department_code, eefsbase.franchise AS franchise_code, fincompany.full_name AS company_description , finbranch.full_name AS branch_description , findepartment.full_name AS department_description , finfranchise.full_name AS franchise_description, eefsbase.sort_key1 AS acct_type_rpt_code, ''::text AS acct_type_rpt_description, eefsbase.sort_key2 AS exec_fs_rptcat2, ''::text AS exec_fs_rptcat2_desc, eefsbase.sort_key3 AS exec_fs_rptcat3, ''::text AS exec_fs_rptcat3_desc, 0 AS financial_report_category, ''::text AS financial_report_cat_desc FROM (SELECT DISTINCT ON (finbalance.dealer_id, finbalance.year_id, finbalance.subledger_id, finbalance.account_id) finbalance.year_id AS year, finbalance.dealer_id AS dealer_id, finbalance.sort_key1 AS sort_key1, finbalance.sort_key2 AS sort_key2, finbalance.sort_key3 AS sort_key3, lpad(finbalance.subledger_id::text,4,'0') AS subledger, lpad(finbalance.account_id::text,4,'0') AS account, lpad(finsubledger.company::text,4,'0') AS company, lpad(finsubledger.department::text,4,'0') AS department, lpad(finsubledger.branch::text,4,'0') AS branch, lpad(finsubledger.franchise::text,4,'0') AS franchise FROM finbalance INNER JOIN finsubledger on ((finbalance.dealer_id=finsubledger.dealer_id) AND (finbalance.year_id=finsubledger.year) AND (finbalance.subledger_id = finsubledger.subledger_number))) eefsbase INNER JOIN fincompanyON (eefsbase.company::int=fincompany.report_number AND eefsbase.dealer_id=fincompany.dealer_id AND eefsbase.year=fincompany.year) INNER JOIN finbranch ON (eefsbase.branch::int=finbranch.report_number AND eefsbase.dealer_id=finbranch.dealer_id AND eefsbase.year=finbranch.year) INNER JOIN findepartment ON (eefsbase.department::int=findepartment.report_number AND eefsbase.dealer_id=findepartment.dealer_id AND eefsbase.year=findepartment.year) INNER JOIN finfranchise ON (eefsbase.franchise::int=finfranchise.report_number AND eefsbase.dealer_id=finfranchise.dealer_id AND eefsbase.year=finfranchise.year); Where in one of my test systems the finbalance table has approximately 220,000 records, around 17,500 of them distinct on the fields mentioned in the distinct clause, the finsubledger table has 97 rows and the other tables mentioned -fincompany,fnbranch,findepartment,finfranchise each have between 1 and 50 records, i.e. relatively small. The above query runs between ten and twelve seconds on this test system and I would like to try and get that down a bit if possible. The explain analyze looks like thus: Unique (cost=19801.92..19801.93 rows=1 width=380) (actual time=10838.666..10884.568 rows=17227 loops=1) - Sort (cost=19801.92..19801.92 rows=1 width=380) (actual time=10838.662..10863.909 rows=17227 loops=1) Sort Key: (((lpad((finsubledger.company)::text, 4, '0'::text)) || (lpad((finsubledger.department)::text, 4, '0'::text))) || (lpad((finsubledger.branch)::text, 4, '0'::text))) || (lpad((finsubledger.franchise)::text, 4, '0'::text))) || (lpad((finbalance.subledger_id)::text, 4, '0'::text))) || (lpad((finbalance.account_id)::text, 4, '0'::text Sort Method: external merge Disk: 2288kB - Nested Loop (cost=19733.14..19801.91 rows=1 width=380) (actual time=9073.324..10386.626 rows=17227 loops=1) - Nested Loop (cost=19733.14..19793.60 rows=1 width=393) (actual time=9073.287..10128.155 rows=17227 loops=1) - Nested Loop (cost=19733.13..19785.30 rows=1 width=336) (actual time=9073.253..9911.426 rows=17227 loops=1) - Nested Loop (cost=19733.13..19777.00 rows=1 width=279) (actual time=9073.222..9685.723 rows=17227 loops=1) - Unique (cost=19733.12..19733.27 rows=12 width=48) (actual
Re: [PERFORM] Optimising a query
Paul Lambert wrote: snip This part of the query alone takes a significant part of the time: SELECT DISTINCT ON (finbalance.dealer_id, finbalance.year_id, finbalance.subledger_id, finbalance.account_id) finbalance.year_id AS year, finbalance.dealer_id AS dealer_id, lpad(finbalance.subledger_id::text,4,'0') AS subledger, lpad(finbalance.account_id::text,4,'0') AS account FROM finbalance Runs with a query plan of : Unique (cost=30197.98..32782.33 rows=20675 width=16) (actual time=5949.695..7197.475 rows=17227 loops=1) - Sort (cost=30197.98..30714.85 rows=206748 width=16) (actual time=5949.691..7018.931 rows=206748 loops=1) Sort Key: dealer_id, year_id, subledger_id, account_id Sort Method: external merge Disk: 8880kB - Seq Scan on finbalance (cost=0.00..8409.70 rows=206748 width=16) (actual time=0.042..617.949 rows=206748 loops=1) Total runtime: 7210.966 ms So basically selecting from the finbalance table (approx. 206,000 records) takes 10 seconds, even longer without the distinct clause in there - the distinct collapses the result-set down to around 17,000 rows. Taking out the two lpad's in there knocks off about 1500ms, so I can come up with something else for them - but I'd like to get the query as a whole down to under a second. dealer_id, year_id, subledger_id and account_id are all part of the primary key on the finbalance table, so I don't think I can index them down any further. Are there any config settings that would make it faster... I'm running on a Quad-core pentium Xeon 1.6GHZ server with 4GB RAM. I imagine shared_buffers (32MB) and work_mem (1MB) could be bumped up a good bit more with 4GB of available RAM? -- Paul Lambert Database Administrator AutoLedgers - A Reynolds Reynolds Company ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
