[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? T

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 > st

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

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

[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] 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 : > Hi, > >>> select from t1 le

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

[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 t1.

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

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 para

[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 query

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). -

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] 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

[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