[PERFORM] Can postgresql plan a query using multiple CPU cores?

Hi, I have a letancy-sensitive legacy application, where the time consumed by query planning was always causing some headaches. Currently it is running on postgresql-8.4 - will postgresql-10 support generating plans using multiple CPU cores to reduce the time required to generate a single plan?

Re: [PERFORM] Any disadvantages of using =ANY(ARRAY()) instead of IN?

Hello Noah, Thanks a lot for your feedback and explanations. Since you have 15+ tables at the top level, the genetic query optimizer should be kicking in and delivering a plan in reasonable time, albeit with plan quality hazards.  There's a danger zone when the deterministic planner is still

Re: [PERFORM] Any disadvantages of using =ANY(ARRAY()) instead of IN?

Hi, I would be really grateful for feedback regardding this issue. Tom? Should Ifile a bug-report about the optimizer trying too hard to collapse the subquery and therefor generating a bad plan? Its my understanding that a IN shouldn't perform any worse than ANY on an ARRAY, right? Thank you in

Re: [PERFORM] Any disadvantages of using =ANY(ARRAY()) instead of IN?

Hi again, That doesn't sound like a tremendously good idea to me. Could you elaborate on the downsides of this approach a bit? Any other thoughts about the pro/cons replacing IN(subquery) with =ANY(ARRAY(subquery))? Are there patological cases, except when the subquery returns a huge amount of

[PERFORM] Any disadvantages of using =ANY(ARRAY()) instead of IN?

Hi, I am using postgresql as database for a hibernate based java oltp project and as in previous projects am totally impressed by postgresql's robustness, performance and feature-richness. Thanks for this excellent piece of software. Quite often Hibernate ends up generating queries with a lot of

Re: [PERFORM] Any disadvantages of using =ANY(ARRAY()) instead of IN?

Hi Tom, Thanks for your reply. What PG version are we talking about here? For development I use 9.1.3, on the production server is 8.4.7 - happens with both cases. That doesn't sound like a tremendously good idea to me. Could you elaborate on the downsides of this approach a bit? But with

Re: [PERFORM] hash semi join caused by IN (select ...)

Hi, Does anybody know why the planner treats = ANY(ARRAY(select ...)) differently than IN(select ...)? Which one is preferable, when I already have a lot of joins? Thanks, Clemens 2011/5/17 Clemens Eisserer linuxhi...@gmail.com: Hi, select from t1 left join t2 WHERE id IN (select

Re: [PERFORM] hash semi join caused by IN (select ...)

Hi, select from t1 left join t2 WHERE id IN (select ) Does it work as expected with one less join?  If so, try increasing join_collapse_limit ... That did the trick - thanks a lot. I only had to increase join_collapse_limit a bit and now get an almost perfect plan. Instead of

[PERFORM] hash semi join caused by IN (select ...)

Hi, I have a quite complex, performance sensitive query in a system with a few (7) joins: select from t1 left join t2 WHERE id IN (select ) For this query the planner evaluates the IN with a hash semi join last, and all the joining is done by hash joins for all rows contained in

Re: [PERFORM] Slow query execution over high latency network

Hi Andrej, Thanks a lot for taking a loot at the tcpdump data. I just had a brief glance over your tcpdump data ... are you sure hibernate isn't using a cursor to fetch each row individually? Pretty sure, yes. I get the same performance when executing the hibernate-generated query using JDBC,

[PERFORM] Slow query execution over high latency network

Hello, When executing huge (10kb), hibernate-generated queries I noticed that when executed remotly over high-latency network (ping to server 200-400ms), the query takes a lot longer to complete. When the query is executed remotly (psql or jdbc) it takes 1800ms to execute, when I issue the

Re: [PERFORM] Slow query execution over high latency network

Hi Pierre, Thanks a lot for your reply. Your attached file didn't come through. Hmm, ok. I uploaded the wireshark-log to: http://93.190.88.182/psql_large_query.bin - different execution plan between your app and ssh+psql, which can happen if the planning uses/doesn't use your specific

Re: [PERFORM] Performance on new 64bit server compared to my 32bit desktop

Hi, This isn't an older Opteron, its 6 core, 6MB L3 cache Istanbul.  Its not the newer stuff either. Everything before Magny Cours is now an older Opteron from my perspective. The 6-cores are identical to Magny Cours (except that Magny Cours has two of those beast in one package). - Clemens

[PERFORM] Are Indices automatically generated for primary keys?

Hi, Are indices for columns marked with PRIMARY KEY automatically generated by postgresql, or do I have to do it manually? The question might seem dumb, I ask because I remember from working with MySQL it generates indices automatically in this case. Thank you in advance, Clemens

Re: [PERFORM] Are Indices automatically generated for primary keys?

Hi, they are generated automatically. Thanks depesz! The reason why I asked was because pgAdmin doesn't display the automatically created indices, which confused me. Thanks, Clemens PS: If you look at the documentation page for CREATE TABLE, you'll see the following . but if you use

[PERFORM] Question about explain-command...

Hello, I just discovered the explain command and well ... have some (for you of course very stupid) questions. I do a quite large (for my taste) join, the query looks like the following: SELECT DISTINCT customer.email AS cemail, customer.key AS ckey, customer.anrede AS canrede, customer.strasse