Re: [PERFORM] PostgreSQL vs MySQL, and FreeBSD
Among other things (FreeBSD advocacy, mostly :) ), it contains a direct comparison between MySQL and PostgreSQL on various platforms, with PostgreSQL winning! Hello, If the queries are complex, this is understable. I had a performance review of a Hibernate project (Java Object Relation Mapping) using MySQL. ORM produces easily complex queries with joins and subqueries. MySQL uses nested loops for subqueries which lead to performance issues with growing database size. They state in their documentation that for version 5.2 there are improvements planned regarding this kind of query. Best Regards Sebastian ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
Mark Kirkwood schrieb: Josh Berkus wrote: Sebastian, Before inventing a hyper tool, we might consider to provide 3-5 example szenarios for common hardware configurations. This consumes less time and be discussed and defined in a couple of days. This is of course not the correct option for a brandnew 20 spindle Sata 10.000 Raid 10 system but these are probably not the target for default configurations. That's been suggested a number of times, but some GUCs are really tied to the *exact* amount of RAM you have available. So I've never seen how example configurations could help. I'm not convinced about this objection - having samples gives a bit of a heads up on *what* knobs you should at least look at changing. Also it might be helpful on the -general or -perf lists to be able to say try config 3 (or whatever we call 'em) and see what changes... I've certainly found the sample config files supplied with that database whose name begins with 'M' a useful *start* when I want something better than default... Cheers Mark Some ideas about szenarios and setting. This is meant as a discussion proposal, I am by far not a database guru! The settings do not provide a perfect setup but a more efficient as compared to default setup. criterias: free memory cpu ? what is the consequence? separate spindels total connections Windows/linux/soloars ? adapted settings: max_connections shared_buffers effective_cache_size /work_mem //maintenance_work_mem /checkpoint_segments ? checkpoint_timeout ? checkpoint_warning ? Szenario a) 256 MB free memory, one disk or raid where all disks are in the raid, max_connections = 40 shared_buffers = 64MB effective_cache_size = 180 MB /work_mem = 1 MB //maintenance_work_mem = 4 MB / Szenario b) 1024 MB free memory, one disk or raid where all disks are in the raid max_connections = 80 shared_buffers = 128 MB effective_cache_size = 600 MB /work_mem = 1,5 MB //maintenance_work_mem = 16 MB / Szenario c) 2048 MB free memory, one disk or raid where all disks are in the raid max_connections = 160 shared_buffers = 256 MB effective_cache_size = 1200 MB /work_mem = 2 MB //maintenance_work_mem = 32 MB / Szenario d) 2048 MB free memory, raid of multiple discs, second raid or disk max_connections = 160 shared_buffers = 256 MB effective_cache_size = 1200 MB /work_mem = 2 MB/ /maintenance_work_mem = 32 MB /WAL on second spindle ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
[EMAIL PROTECTED] schrieb: On Tue, 1 May 2007, Carlos Moreno wrote: large problem from a slog perspective; there is no standard way even within Linux to describe CPUs, for example. Collecting available disk space information is even worse. So I'd like some help on this portion. Quite likely, naiveness follows... But, aren't things like /proc/cpuinfo , /proc/meminfo, /proc/partitions / /proc/diskstats standard, at the very least across Linux distros? I'm not familiar with BSD or other Unix flavours, but I would expect these (or their equivalent) to exist in those, no? Am I just being naive? unfortunantly yes. across different linux distros they are fairly standard (however different kernel versions will change them) however different kernels need drasticly different tools to get the info from them. David Lang Before inventing a hyper tool, we might consider to provide 3-5 example szenarios for common hardware configurations. This consumes less time and be discussed and defined in a couple of days. This is of course not the correct option for a brandnew 20 spindle Sata 10.000 Raid 10 system but these are probably not the target for default configurations. If we carefully document these szenario they would we a great help for people having some hardware between the szenarios. Sebastian Hennebrueder ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Feature Request --- was: PostgreSQL Performance Tuning
Josh Berkus schrieb: Sebastian, Before inventing a hyper tool, we might consider to provide 3-5 example szenarios for common hardware configurations. This consumes less time and be discussed and defined in a couple of days. This is of course not the correct option for a brandnew 20 spindle Sata 10.000 Raid 10 system but these are probably not the target for default configurations. That's been suggested a number of times, but some GUCs are really tied to the *exact* amount of RAM you have available. So I've never seen how example configurations could help. I would define the szenario as 256 MB freely available for PostgresQL = setting x can be of size ... ---(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] effizient query with jdbc
You could issue one query containing a
select uuid FROM MDM.KEYWORDS_INFO WHERE KEYWORDS_ID in (xy)
where xy is a large comma separated list of your values.
Best Regards / Viele Grüße
Sebastian Hennebrueder
-
http://www.laliluna.de
* Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB
* Seminars and Education at reasonable prices
* Get professional support and consulting for these technologies
Johannes Bühler schrieb:
Hi,
I have a java.util.List of values (1) which i wanted to use for a
query in the where clause of an simple select statement. iterating
over the list and and use an prepared Statement is quite slow. Is
there a more efficient way to execute such a query. Thanks for any
help. Johannes
.
List ids = new ArrayList();
List is filled with 1 values ...
List uuids = new ArrayList();
PreparedStatement pstat = db.prepareStatement(SELECT UUID FROM
MDM.KEYWORDS_INFO WHERE KEYWORDS_ID = ?); for (Iterator iter =
ids.iterator(); iter.hasNext();) { String id = (String) iter.next();
pstat.setString(1, id);
rs = pstat.executeQuery();
if (rs.next()) {
uuids.add(rs.getString(1));
}
rs.close();
}
...
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [PERFORM] Massive performance issues
Matthew Sackman schrieb: Hi, I'm having performance issues with a table consisting of 2,043,133 rows. The schema is: \d address Table public.address Column| Type | Modifiers --++--- postcode_top | character varying(2) | not null postcode_middle | character varying(4) | not null postcode_bottom | character varying(7) | not null postcode | character varying(10) | not null property_type| character varying(15) | not null sale_type| character varying(10) | not null flat_extra | character varying(100) | not null number | character varying(100) | not null street | character varying(100) | not null locality_1 | character varying(100) | not null locality_2 | character varying(100) | not null city | character varying(100) | not null county | character varying(100) | not null Indexes: address_city_index btree (city) address_county_index btree (county) address_locality_1_index btree (locality_1) address_locality_2_index btree (locality_2) address_pc_bottom_index btree (postcode_bottom) address_pc_middle_index btree (postcode_middle) address_pc_top_index btree (postcode_top) address_pc_top_middle_bottom_index btree (postcode_top, postcode_middle, postcode_bottom) address_pc_top_middle_index btree (postcode_top, postcode_middle) address_postcode_index btree (postcode) address_property_type_index btree (property_type) address_street_index btree (street) street_prefix btree (lower(substring((street)::text, 1, 1))) This is with postgresql 7.4 running on linux 2.6.11 with a 3GHz P4 and a SATA harddrive. Queries such as: select locality_2 from address where locality_2 = 'Manchester'; are taking 14 seconds to complete, and this is only 2 years worth of data - we will have up to 15 years (so over 15 million rows). Interestingly, doing: explain select locality_2 from address where locality_2 = 'Manchester'; gives QUERY PLAN Seq Scan on address (cost=0.00..80677.16 rows=27923 width=12) Filter: ((locality_2)::text = 'Manchester'::text) but: explain select locality_1 from address where locality_1 = 'Manchester'; gives QUERY PLAN Index Scan using address_locality_1_index on address (cost=0.00..69882.18 rows=17708 width=13) Index Cond: ((locality_1)::text = 'Manchester'::text) Sadly, using the index makes things worse, the query taking 17 seconds. locality_1 has 16650 distinct values and locality_2 has 1156 distinct values. Whilst the locality_2 query is in progress, both the disk and the CPU are maxed out with the disk constantly reading at 60MB/s and the CPU rarely dropping under 100% load. With the locality_1 query in progress, the CPU is maxed out but the disk is reading at just 3MB/s. Obviously, to me, this is a problem, I need these queries to be under a second to complete. Is this unreasonable? What can I do to make this go faster? I've considered normalising the table but I can't work out whether the slowness is in dereferencing the pointers from the index into the table or in scanning the index in the first place. And normalising the table is going to cause much pain when inserting values and I'm not entirely sure if I see why normalising it should cause a massive performance improvement. Just an idea: When you do not want to adapt your application to use a normalized database you may push the data into normalized table using triggers. Example: Add a table city with column id, name and add a column city_id to your main table. In this case you have redundant data in your main table (locality_1 and city_id) but you could make queries to the city table when searching for 'Man%' -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[PERFORM] Looking for a large database for testing
Hello, I would like to test the performance of my Java/PostgreSQL applications especially when making full text searches. For this I am looking for a database with 50 to 300 MB having text fields. e.g. A table with books with fields holding a comment, table of content or example chapters or what ever else. Does anybody have an idea where I can find a database like this or does even have something like this? -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Looking for a large database for testing
Tino Wildenhain schrieb: Sebastian Hennebrueder schrieb: Hello, I would like to test the performance of my Java/PostgreSQL applications especially when making full text searches. For this I am looking for a database with 50 to 300 MB having text fields. e.g. A table with books with fields holding a comment, table of content or example chapters or what ever else. Does anybody have an idea where I can find a database like this or does even have something like this? You can download the wikipedia content. Just browse the wikimedia site. Its some work to change the data to be able to import into postgres, but at least you have a lot real world data - in many languages. I have just found it. Here there is a link http://download.wikimedia.org/ They have content in multiple languages and dumps up to 20 GB. -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PERFORM] Mirroring PostgreSQL database
Shashi Kanth Boddula schrieb: Hi, I have one customer who is using PostgreSQL 7.4.8 on Linux . He has some problems with database mirroring . The details are follows. The customer is using Linux on which PostgreSQL 7.4.8 along with Jboss 3.2.3 is running . He has 2 servers , one is acting as a live server (primary) and another is acting as a fail-over (secondary) server . Secondary server is placed in remote location . These servers are acting as a Attendence server for daily activities . Nearly 50,000 employees depend on the live server . The customer is using DBmirror tool to mirror the database records of primary to secondary . The customer is complaining that there is one day (24 hours) delay between primary and secondray for database synchronization . They have dedicated line and bandwidth , but still the problems exists. I just want to know , for immediate data mirroring , what is the best way for PostgreSQL . PostgreSQL is offering many mirror tools , but which one is the best ?. Is there any other way to accomplish the task ? Thank you . Waiting for your reply. Thanks Regards, Shashi Kanth Consultant - Linux RHCE , LPIC-2 Onward Novell - Bangalore 9886455567 For java based solution you could also have a look at x-jdbc or xjdbc. But before you should find out what the reason for the delay is actually. When the backup server is to slow, it may be not important which mirroring tool you use. -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] tricky query
John A Meinel schrieb: John A Meinel wrote: Well, I was able to improve it to using appropriate index scans. Here is the query: SELECT t1.id+1 as id_new FROM id_test t1 WHERE NOT EXISTS (SELECT t2.id FROM id_test t2 WHERE t2.id = t1.id+1) ORDER BY t1.id LIMIT 1; I created a test table which has 90k randomly inserted rows. And this is what EXPLAIN ANALYZE says: As Cosimo stated the result can be wrong. The result is always wrong when the id with value 1 does not exist. -- Best Regards / Viele Grüße Sebastian Hennebrueder http://www.laliluna.de Tutorials for JSP, JavaServer Faces, Struts, Hibernate and EJB Get support, education and consulting for these technologies - uncomplicated and cheap. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Query plan for very large number of joins
Tom Lane schrieb: Richard Huxton dev@archonet.com writes: [EMAIL PROTECTED] wrote: I am using PostgreSQL (7.4) with a schema that was generated automatically (using hibernate). The schema consists of about 650 relations. One particular query (also generated automatically) consists of left joining approximately 350 tables. May I be the first to offer an ouch! Seconded. However, I'm not sure how much leeway there is in planning a largely left-joined query. Not much. The best hope for a better result is to order the LEFT JOIN clauses in a way that will produce a good plan. If this is the best way, you should consider to use an sql query and not the hibernate ql language in this case. This is possible with Hibernate! I suppose you could also consider a view in Postgre and let Hibernate read from this view. This is also possible. One thought is that I am not sure I believe the conclusion that planning is taking only 36 ms; even realizing that the exclusive use of left joins eliminates options for join order, there are still quite a lot of plans to consider. You should try both EXPLAIN and EXPLAIN ANALYZE from psql and see how long each takes. It'd also be interesting to keep an eye on how large the backend process grows while doing this --- maybe it's being driven into swap. Also: I'm not sure there *is* such a thing as a good plan for a 350-way join. It may be time to reconsider your data representation. If Hibernate really forces this on you, it may be time to reconsider your choice of tool. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Kind Regards / Viele Grüße Sebastian Hennebrueder - http://www.laliluna.de/tutorials.html Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more. ---(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] Optimize complex join to use where condition before
I found a solution to improve my query. I do not know why but the statistics for all column has been 0. I changed this to 10 for index columns and to 20 for all foreign key columns. and to 100 for foreign key columns. I set the random page cost to 2 and now the query runs as expected. Many thanks to all of the posts in my and in other threads which helped a lot. Sebastian Merge Join (cost=1325.06..1329.96 rows=6 width=2558) (actual time=344.000..344.000 rows=6 loops=1) Merge Cond: (outer.fid = inner.faufgaben_id) - Sort (cost=1269.57..1271.91 rows=934 width=2541) (actual time=344.000..344.000 rows=773 loops=1) Sort Key: taufgaben.fid - Merge Join (cost=1205.09..1223.49 rows=934 width=2541) (actual time=219.000..313.000 rows=936 loops=1) Merge Cond: (outer.fid = inner.fprojekt_id) - Sort (cost=302.08..304.27 rows=876 width=1494) (actual time=156.000..156.000 rows=876 loops=1) Sort Key: tprojekte.fid - Merge Join (cost=237.42..259.27 rows=876 width=1494) (actual time=109.000..141.000 rows=876 loops=1) Merge Cond: (outer.fid = inner.fprojektleiter_id) - Index Scan using pk_tuser on tuser (cost=0.00..9.13 rows=109 width=883) (actual time=0.000..0.000 rows=101 loops=1) - Sort (cost=237.42..239.61 rows=876 width=619) (actual time=109.000..109.000 rows=876 loops=1) Sort Key: tprojekte.fprojektleiter_id - Merge Join (cost=181.17..194.60 rows=876 width=619) (actual time=63.000..94.000 rows=876 loops=1) Merge Cond: (outer.fid = inner.fkunden_kst_id) - Sort (cost=9.51..9.66 rows=58 width=119) (actual time=0.000..0.000 rows=58 loops=1) Sort Key: tkunden_kst.fid - Merge Join (cost=6.74..7.81 rows=58 width=119) (actual time=0.000..0.000 rows=58 loops=1) Merge Cond: (outer.fid = inner.fkunden_id) - Sort (cost=3.46..3.56 rows=40 width=51) (actual time=0.000..0.000 rows=40 loops=1) Sort Key: tkunden.fid - Seq Scan on tkunden (cost=0.00..2.40 rows=40 width=51) (actual time=0.000..0.000 rows=40 loops=1) - Sort (cost=3.28..3.42 rows=58 width=80) (actual time=0.000..0.000 rows=58 loops=1) Sort Key: tkunden_kst.fkunden_id - Seq Scan on tkunden_kst (cost=0.00..1.58 rows=58 width=80) (actual time=0.000..0.000 rows=58 loops=1) - Sort (cost=171.66..173.85 rows=876 width=508) (actual time=63.000..63.000 rows=876 loops=1) Sort Key: tprojekte.fkunden_kst_id - Merge Join (cost=114.91..128.85 rows=876 width=508) (actual time=31.000..47.000 rows=876 loops=1) Merge Cond: (outer.fid = inner.fkostentraeger_id) - Sort (cost=19.20..19.60 rows=158 width=162) (actual time=0.000..0.000 rows=158 loops=1) Sort Key: tkostentraeger.fid - Merge Join (cost=3.49..13.43 rows=158 width=162) (actual time=0.000..0.000 rows=158 loops=1) Merge Cond: (outer.fkostenstellen_id = inner.fid) - Index Scan using idx_kostenstellen_id on tkostentraeger (cost=0.00..7.18 rows=158 width=55) (actual time=0.000..0.000 rows=158 loops=1) - Sort (cost=3.49..3.53 rows=19 width=119) (actual time=0.000..0.000 rows=158 loops=1) Sort Key: tkostenstellen.fid - Merge Join (cost=2.76..3.08 rows=19 width=119) (actual time=0.000..0.000 rows=19 loops=1) Merge Cond: (outer.fid = inner.fabteilungen_id) - Sort (cost=1.17..1.19 rows=7 width=76) (actual time=0.000..0.000 rows=7 loops=1) Sort Key: tabteilungen.fid - Seq Scan on tabteilungen (cost=0.00..1.07 rows=7 width=76) (actual time=0.000..0.000 rows=7 loops=1) - Sort (cost=1.59..1.64 rows=19 width=55) (actual time=0.000..0.000 rows=19 loops=1) Sort Key: tkostenstellen.fabteilungen_id - Seq Scan on tkostenstellen (cost=0.00..1.19 rows=19 width=55) (actual time=0.000..0.000 rows=19 loops=1) - Sort (cost=95.71..97.90 rows=878 width=354) (actual time=31.000..31.000 rows=877 loops=1) Sort Key: tprojekte.fkostentraeger_id - Seq Scan on tprojekte (cost=0.00..52.78 rows=878 width=354) (actual time=0.000..31.000 rows=878 loops=1) - Sort (cost=903.01..905.35 rows=936 width=1047) (actual time=63.000..63.000 rows=936 loops=1) Sort Key: taufgaben.fprojekt_id - Nested Loop Left Join (cost=0.28..856.82 rows=936 width=1047) (actual time=0.000..63.000 rows=936 loops=1) Join Filter: (outer.fid = inner.faufgaben_id) - Index Scan using idx_taufgaben_bstatus on taufgaben (cost=0.00..835.47 rows=936 width=1043) (actual time=0.000..0.000 rows=936 loops=1) Index Cond: (fbearbeitungsstatus 2) - Materialize (cost=0.28..0.29 rows=1 width=4) (actual time=0.000..0.000 rows=1 loops=936) - Subquery Scan patchdaten (cost=0.00..0.28 rows=1
[PERFORM] Optimize complex join to use where condition before join
Hello, I am facing a problem in optimizing the query shown below. Most queries in the application do only find about 20 to 100 matching rows. The query joins the table taufgaben_mitarbeiter to taufgaben on which a condition like the following where clause is frequently used. where am.fmitarbeiter_id = 54 then there is a nested join to taufgaben - tprojekt - tkunden_kst - tkunden. What I would like to achieve is that before joining all the tables that the join of taufgaben_mitarbeiter (... from taufgaben left join taufgaben_mitarbeiter am on taufgaben.fid = am.faufgaben_id) is done and that the where condition is evaluated. Than an index scan to join the other data is run. What is happening at the moment (if I understood the explain analyze) is that the full join is done and at the end the where condition is done. The query with seqscan and nestloop enabled takes about 3 seconds. The query with both disabled takes 0.52 seconds The query with only nestlop disabled takes 0.6 seconds and with only sesscan disabled takes about 3 seconds. Below you can find the explain analyze from seqscan and nestloop enabled and from both disabled. The problem seems to be right at the beginning when the rows are badly estimated. ... Merge Cond: (outer.fid = inner.faufgaben_id) - Nested Loop (cost=1621.51..1729.28 rows=6 width=2541) (actual time=328.000..3125.000 rows=1118 loops=1) ... I am using PostgreSQL 8.0 on Windows Thank you for any idea -- Kind Regards / Viele Grüße Sebastian Hennebrueder - http://www.laliluna.de/tutorials.html Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more. enabled seqscan and nested_loop explain analyze SELECT taufgaben.fid AS taufgaben_fid, taufgaben.fprojekt_id AS taufgaben_fprojekt_id, taufgaben.fnummer AS taufgaben_fnummer, taufgaben.fbudget AS taufgaben_fbudget, taufgaben.ftyp AS taufgaben_ftyp, taufgaben.fberechnungsart AS taufgaben_fberechnungsart, taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern, taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis, taufgaben.fstatus AS taufgaben_fstatus, taufgaben.fkurzbeschreibung AS taufgaben_fkurzbeschreibung, taufgaben.fansprechpartner AS taufgaben_fansprechpartner, taufgaben.fanforderer AS taufgaben_fanforderer, taufgaben.fstandort_id AS taufgaben_fstandort_id, taufgaben.fwunschtermin AS taufgaben_fwunschtermin, taufgaben.fstarttermin AS taufgaben_fstarttermin, taufgaben.fgesamtaufwand AS taufgaben_fgesamtaufwand, taufgaben.fistaufwand AS taufgaben_fistaufwand, taufgaben.fprio AS taufgaben_fprio, taufgaben.ftester AS taufgaben_ftester, taufgaben.ffaellig AS taufgaben_ffaellig, taufgaben.flevel AS taufgaben_flevel, taufgaben.fkategorie AS taufgaben_fkategorie, taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung, taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus, taufgaben.fsolllimit AS taufgaben_fsolllimit, taufgaben.fistlimit AS taufgaben_fistlimit, taufgaben.fpauschalbetrag AS taufgaben_fpauschalbetrag, taufgaben.frechnungslaeufe_id AS taufgaben_frechnungslaeufe_id, taufgaben.fzuberechnen AS taufgaben_fzuberechnen, tprojekte.fid AS tprojekte_fid, tprojekte.fbezeichnung AS tprojekte_fbezeichnung, tprojekte.fprojektnummer AS tprojekte_fprojektnummer, tprojekte.fbudget AS tprojekte_fbudget, tprojekte.fverrechnung_extern AS tprojekte_fverrechnung_extern, tprojekte.fstatus AS tprojekte_fstatus, tprojekte.fkunden_kst_id AS tprojekte_fkunden_kst_id, tprojekte.fverrechnungsbasis AS tprojekte_fverrechnungsbasis, tprojekte.fberechnungsart AS tprojekte_fberechnungsart, tprojekte.fprojekttyp AS tprojekte_fprojekttyp, tprojekte.fkostentraeger_id AS tprojekte_fkostentraeger_id, tprojekte.fprojektleiter_id AS tprojekte_fprojektleiter_id, tprojekte.fpauschalsatz AS tprojekte_fpauschalsatz, tprojekte.frechnungslaeufe_id AS tprojekte_frechnungslaeufe_id, tprojekte.fzuberechnen AS tprojekte_fzuberechnen, tprojekte.faufschlagrel AS tprojekte_faufschlagrel, tprojekte.faufschlagabs AS tprojekte_faufschlagabs, tprojekte.fbearbeitungsstatus AS tprojekte_fbearbeitungsstatus, tuser.fusername AS tuser_fusername, tuser.fpassword AS tuser_fpassword, tuser.fvorname AS tuser_fvorname, tuser.fnachname AS tuser_fnachname, tuser.fismitarbeiter AS tuser_fismitarbeiter, tuser.flevel AS tuser_flevel, tuser.fkuerzel AS tuser_fkuerzel, taufgaben.floesungsbeschreibung AS taufgaben_floesungsbeschreibung, taufgaben.ffehlerbeschreibung AS taufgaben_ffehlerbeschreibung, taufgaben.faufgabenstellung AS taufgaben_faufgabenstellung, taufgaben.fkritischeaenderungen AS taufgaben_fkritischeaenderungen, taufgaben.fbdeaufgabenersteller_id AS taufgaben_fbdeaufgabenersteller_id, taufgaben.fzufaktorieren AS taufgaben_fzufaktorieren, tprojekte.fzufaktorieren AS tprojekte_fzufaktorieren, taufgaben.fisdirty AS taufgaben_fisdirty, taufgaben.fnf_kunde_stunden
Re: [PERFORM] Optimize complex join to use where condition before
Solution to my problem. I added indexes to each foreign_key (there had been some missing). I will try tomorrow by daylight what influence this had actually. Only the indexes did not change anything! Even with lower random_page_costs and higher shared mem. The big change was the following I created a view which holds a part of the query. The part is the nested join I am doing from rpojekt, tkunden_kst, See below Than I changed my query to include the view which improved the performance from 3000 to 450 ms which is quite good now. But I am having two more question a) ### I estimated the theoretical speed a little bit higher. The query without joining the view takes about 220 ms. A query to the view with a condition projekt_id in ( x,y,z), beeing x,y,z all the projekt I got with the first query, takes 32 ms. So my calculation is query a 220 query b to view with project in ... 32 = 252 ms + some time to add the adequate row from query b to one of the 62 rows from query a This sometime seems to be quite high with 200 ms or alternative query a 220 ms for each of the 62 rows a query to the view with project_id = x 220 62*2 ms = 344 ms + some time to assemble all this. = 100 ms for assembling. This is quite a lot or am I wrong b) ### My query does take about 200 ms. Most of the time is taken by the following part LEFT JOIN ( SELECT DISTINCT taufgaben_patches.faufgaben_id FROM taufgaben_patches ORDER BY taufgaben_patches.faufgaben_id ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id What I want to achieve is one column in my query beeing null or not null and indicating if there is a patch which includes the aufgabe (engl.: task) Is there a better way? -- Kind Regards / Viele Grüße Sebastian Hennebrueder - http://www.laliluna.de/tutorials.html Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more. ## Below you can find query solution I found explain analyze of the complete query (my solution) explain analyze of query a explain analyze of view with one project_id as condition explain analyze SELECT taufgaben.fid AS taufgaben_fid, taufgaben.fprojekt_id AStaufgaben_fprojekt_id, taufgaben.fnummer AS taufgaben_fnummer, taufgaben.fbudget AS taufgaben_fbudget, taufgaben.ftyp AS taufgaben_ftyp, taufgaben.fberechnungsart AS taufgaben_fberechnungsart, taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern, taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis, taufgaben.fstatus AS taufgaben_fstatus, taufgaben.fkurzbeschreibung AS taufgaben_fkurzbeschreibung, taufgaben.fansprechpartner AS taufgaben_fansprechpartner, taufgaben.fanforderer AS taufgaben_fanforderer, taufgaben.fstandort_id AS taufgaben_fstandort_id, taufgaben.fwunschtermin AS taufgaben_fwunschtermin, taufgaben.fstarttermin AS taufgaben_fstarttermin, taufgaben.fgesamtaufwand AS taufgaben_fgesamtaufwand, taufgaben.fistaufwand AS taufgaben_fistaufwand, taufgaben.fprio AS taufgaben_fprio, taufgaben.ftester AS taufgaben_ftester, taufgaben.ffaellig AS taufgaben_ffaellig, taufgaben.flevel AS taufgaben_flevel, taufgaben.fkategorie AS taufgaben_fkategorie, taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung, taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus, taufgaben.fsolllimit AS taufgaben_fsolllimit, taufgaben.fistlimit AS taufgaben_fistlimit, taufgaben.fpauschalbetrag AS taufgaben_fpauschalbetrag, taufgaben.frechnungslaeufe_id AS taufgaben_frechnungslaeufe_id, taufgaben.fzuberechnen AS taufgaben_fzuberechnen, taufgaben.floesungsbeschreibung AS taufgaben_floesungsbeschreibung, taufgaben.ffehlerbeschreibung AS taufgaben_ffehlerbeschreibung, taufgaben.faufgabenstellung AS taufgaben_faufgabenstellung, taufgaben.fkritischeaenderungen AS taufgaben_fkritischeaenderungen, taufgaben.fbdeaufgabenersteller_id AS taufgaben_fbdeaufgabenersteller_id, taufgaben.fzufaktorieren AS taufgaben_fzufaktorieren, taufgaben.fisdirty AS taufgaben_fisdirty, taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden, taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden, taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden, taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag, taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag, taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag, taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden, taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag, taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet, taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet, taufgaben.fnfgesamtaufwand AS taufgaben_fnfgesamtaufwand, taufgaben.fnf_netto_stunden AS taufgaben_fnf_netto_stunden, taufgaben.fnf_brutto_stunden AS taufgaben_fnf_brutto_stunden, taufgaben.fnfhinweisgesendet AS taufgaben_fnfhinweisgesendet, taufgaben.fnfwarnunggesendet
Re: [PERFORM] Optimize complex join to use where condition before
Solution not found as I thought. I integrated the query in a view and the query plan became very bad once again. The reason is that when I am using the view I have the joins in a differerent order. Does anyone have an idea to solve this. Sebastian a) bad order but the one I have in my application explain analyze SELECT taufgaben.fid AS taufgaben_fid, taufgaben.fprojekt_id AS taufgaben_fprojekt_id, taufgaben.fnummer AS taufgaben_fnummer, taufgaben.fbudget AS taufgaben_fbudget, taufgaben.ftyp AS taufgaben_ftyp, taufgaben.fberechnungsart AS taufgaben_fberechnungsart, taufgaben.fverrechnung_extern AS taufgaben_fverrechnung_extern, taufgaben.fverrechnungsbasis AS taufgaben_fverrechnungsbasis, taufgaben.fstatus AS taufgaben_fstatus, taufgaben.fkurzbeschreibung AS taufgaben_fkurzbeschreibung, taufgaben.fansprechpartner AS taufgaben_fansprechpartner, taufgaben.fanforderer AS taufgaben_fanforderer, taufgaben.fstandort_id AS taufgaben_fstandort_id, taufgaben.fwunschtermin AS taufgaben_fwunschtermin, taufgaben.fstarttermin AS taufgaben_fstarttermin, taufgaben.fgesamtaufwand AS taufgaben_fgesamtaufwand, taufgaben.fistaufwand AS taufgaben_fistaufwand, taufgaben.fprio AS taufgaben_fprio, taufgaben.ftester AS taufgaben_ftester, taufgaben.ffaellig AS taufgaben_ffaellig, taufgaben.flevel AS taufgaben_flevel, taufgaben.fkategorie AS taufgaben_fkategorie, taufgaben.feintragbearbeitung AS taufgaben_feintragbearbeitung, taufgaben.fbearbeitungsstatus AS taufgaben_fbearbeitungsstatus, taufgaben.fsolllimit AS taufgaben_fsolllimit, taufgaben.fistlimit AStaufgaben_fistlimit, taufgaben.fpauschalbetrag AS taufgaben_fpauschalbetrag, taufgaben.frechnungslaeufe_id AS taufgaben_frechnungslaeufe_id, taufgaben.fzuberechnen AS taufgaben_fzuberechnen, taufgaben.floesungsbeschreibung AS taufgaben_floesungsbeschreibung, taufgaben.ffehlerbeschreibung AStaufgaben_ffehlerbeschreibung, taufgaben.faufgabenstellung AS taufgaben_faufgabenstellung, taufgaben.fkritischeaenderungen AStaufgaben_fkritischeaenderungen, taufgaben.fbdeaufgabenersteller_id AS taufgaben_fbdeaufgabenersteller_id, taufgaben.fzufaktorieren AStaufgaben_fzufaktorieren, taufgaben.fisdirty AS taufgaben_fisdirty, taufgaben.fnf_kunde_stunden AS taufgaben_fnf_kunde_stunden, taufgaben.fzf_kunde_stunden AS taufgaben_fzf_kunde_stunden, taufgaben.fbf_kunde_stunden AS taufgaben_fbf_kunde_stunden, taufgaben.fnf_kunde_betrag AS taufgaben_fnf_kunde_betrag, taufgaben.fzf_kunde_betrag AS taufgaben_fzf_kunde_betrag, taufgaben.fbf_kunde_betrag AS taufgaben_fbf_kunde_betrag, taufgaben.fgesamt_brutto_stunden AS taufgaben_fgesamt_brutto_stunden, taufgaben.fgesamt_brutto_betrag AS taufgaben_fgesamt_brutto_betrag, taufgaben.fhinweisgesendet AS taufgaben_fhinweisgesendet, taufgaben.fwarnunggesendet AS taufgaben_fwarnunggesendet, taufgaben.fnfgesamtaufwand AS taufgaben_fnfgesamtaufwand, taufgaben.fnf_netto_stunden AStaufgaben_fnf_netto_stunden, taufgaben.fnf_brutto_stunden AS taufgaben_fnf_brutto_stunden, taufgaben.fnfhinweisgesendet AS taufgaben_fnfhinweisgesendet, taufgaben.fnfwarnunggesendet AStaufgaben_fnfwarnunggesendet, taufgaben.fhatzeiten AS taufgaben_fhatzeiten, taufgaben.fnichtpublicrechnungsfaehig AS taufgaben_fnichtpublicrechnungsfaehig, taufgaben.fnichtpublicrechnungsfaehigbetrag AS taufgaben_fnichtpublicrechnungsfaehigbetrag, taufgaben.fnichtberechenbar AStaufgaben_fnichtberechenbar, taufgaben.fnichtberechenbarbetrag AS taufgaben_fnichtberechenbarbetrag, taufgaben.finternertester AS taufgaben_finternertester, taufgaben.finterngetestet AS taufgaben_finterngetestet, taufgaben.fanzahlbearbeiter AS taufgaben_fanzahlbearbeiter, patchdaten.faufgaben_id AS pataid ,vprojekt.* FROM taufgaben LEFT JOIN ( SELECT DISTINCT taufgaben_patches.faufgaben_id FROM taufgaben_patches ) patchdaten ON taufgaben.fid = patchdaten.faufgaben_id JOIN vprojekt ON taufgaben.fprojekt_id = vprojekt.tprojekte_fid join taufgaben_mitarbeiter am on taufgaben.fid = am.faufgaben_id where am.fmitarbeiter_id = 54 and taufgaben.fbearbeitungsstatus 2 Nested Loop (cost=1349.13..1435.29 rows=1 width=2541) (actual time=1640.000..3687.000 rows=62 loops=1) Join Filter: (inner.fid = outer.faufgaben_id) - Index Scan using idx_tauf_mit_mitid on taufgaben_mitarbeiter am (cost=0.00..80.65 rows=35 width=4) (actual time=0.000..0.000 rows=765 loops=1) Index Cond: (fmitarbeiter_id = 54) - Materialize (cost=1349.13..1349.20 rows=7 width=2541) (actual time=0.531..1.570 rows=1120 loops=765) - Merge Join (cost=1343.42..1349.13 rows=7 width=2541) (actual time=406.000..515.000 rows=1120 loops=1) Merge Cond: (outer.fid = inner.fprojekt_id) - Sort (cost=130.89..130.90 rows=6 width=1494) (actual time=203.000..203.000 rows=876
[PERFORM] Recommendations for set statistics
Hello, I could not find any recommandations for the level of set statistics and what a specific level does actually mean. What is the difference between 1, 50 and 100? What is recommanded for a table or column? -- Kind Regards / Viele Grüße Sebastian Hennebrueder - http://www.laliluna.de/tutorials.html Tutorials for Java, Struts, JavaServer Faces, JSP, Hibernate, EJB and more. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
