Re: Finding out why parallel queries not avoided
> On 22 Jul 2018, at 05:45, David Rowley wrote: > > On 21 July 2018 at 20:15, Didier Carlier wrote: >> explain select count(*) from calendar c1, calendar c2, measure m where >> c1.stddate='2015-01-01' and c2.stddate='2015-12-31' and m.fromdateid >> >=c1.calendarid and m.fromdateid < c2.calendarid; >> QUERY PLAN >> -- >> Aggregate (cost=5073362.73..5073362.74 rows=1 width=8) >> -> Nested Loop (cost=8718.47..4988195.81 rows=34066770 width=0) >> -> Index Scan using calendar_stddate_unique on calendar c2 >> (cost=0.28..2.30 rows=1 width=4) >> Index Cond: (stddate = '2015-12-31 00:00:00+01'::timestamp >> with time zone) >> -> Nested Loop (cost=8718.19..4647525.81 rows=34066770 width=4) >> -> Index Scan using calendar_stddate_unique on calendar c1 >> (cost=0.28..2.30 rows=1 width=4) >> Index Cond: (stddate = '2015-01-01 >> 00:00:00+01'::timestamp with time zone) >> -> Bitmap Heap Scan on measure m (cost=8717.91..4306855.81 >> rows=34066770 width=4) >> Recheck Cond: ((fromdateid >= c1.calendarid) AND >> (fromdateid < c2.calendarid)) >> -> Bitmap Index Scan on idx_measure_fromdate >> (cost=0.00..201.22 rows=34072527 width=0) >> Index Cond: ((fromdateid >= c1.calendarid) AND >> (fromdateid < c2.calendarid)) >> >> Both queries return the same answers but I don't see why the second one >> doesn't use parallel query. > > You'd likely be better of writing the query as: > > select count(*) from measure where fromdateid >= (select calendarid > from calendar where stddate = '2015-01-01') and fromdateid < (select > calendarid from calendar where stddate = '2015-12-31'); > > The reason you get the poor nested loop plan is that nested loop is > the only join method that supports non-equijoin. It doesn’t use a parallel query but It’s faster indeed, (~12 sec vs 9sec), thanks for the info. > > Unsure why you didn't get a parallel plan. Parallel in pg10 supports a > few more plan shapes than 9.6 did. Unsure what version you're using. It’s on 10.3 which is the latest available package prebuilt for SmartOS
Re: Call for Papers - PGConf.ASIA 2018
Hi PostgreSQL lovers, The call for papers for PGConf.ASIA 2018 will be closed on 31st July, 2018 (Japan time). I am looking forward to receiving your great proposals! -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese:http://www.sraoss.co.jp > Hi, > > PGconf.ASIA 2018 will be held on December 10 to 12 in Tokyo and we are > now accepting proposals for talks. > > Join us and other users and developers from around the world at the > home of the oldest and largest user group; Japan! > > (See http://www.pgconf.asia/EN/2017/program/ for last year's > conference program). > > - About the conference: > Location: Akihabara convention hall (Tokyo) > http://www.akibahall.jp/data/outline_eng.html > Targeted number of attendees: 700 in total > http://www.pgconf.asia/EN/2018/ > > - About the call for papers: > > - To submit a paper, please include the following details and send to: > pgconf-asia-2018-submission(at)pgconf(dot)asia > > Title > Abstract > Description > Language spoken during the talk: Japanese or English > Language of the talk material: Japanese and/or English > > - Submission deadline is midnight, 31st July, 2018 (Japan time). > > - Submissions should be sent in English, Japanese, or both. > Japanese-only submissions will be translated into English for > discussion within the program committee. > > - Presentation materials will be released the day after the conference > and will be made available to the public. The copyright of the > material will be retained by the author. > > We ask that you share your materials under a Creative Commons > license: https://creativecommons.org/licenses/by-nc-nd/4.0/ > > If your presentation material cannot be shared, please let us know. > > - Talks may be recorded or photographed. In both cases, the content > may be made public under a Creative Commons license: > https://creativecommons.org/licenses/by-nc-nd/4.0/. The copyright > of the material is retained by the speaker. > > - Speakers will be informed of the result of the selection by the end > of August. Speakers will be requested to submit brief biographies and > photos to be published in the conference program. > > - The exact length of each session is not decided yet (Last year it > was 40 minutes and we expect no big change for this year). > > Suggested topic areas include but not limited to: > > - Large-scale PostgreSQL deployments. > - Migrations from other databases to PostgreSQL > - Operations and administration > - Performance and feature implementation > - Replication, clustering, HA, sharding. > - Tools and utilities for PostgreSQL > - Benchmarking and hardware, tuning. > - PostgreSQL community and hacking. > - Studies, surveys on PostgreSQL ecosystem > - Asian PostgreSQL community & user groups > - Data warehousing > - Location-aware and mapping software with PostGIS > - Research and teaching with PostgreSQL > - Case studies, including but not limited to: IoT/Cloud, Healthcare, > Education and Academy > > - If you have any questions regarding the event, feel free to contact > the organization committee at > pgconf-asia-2018-submission(at)pgconf(dot)asia(dot) > > See you in Tokyo :) > > PGConf.ASIA 2018 Steering Committee > http://www.pgconf.asia/EN/2018/ > [Please feel free to redistribute this CFP] > From: Tatsuo Ishii Subject: Call for Papers - PGConf.ASIA 2018 Date: Mon, 04 Jun 2018 13:29:20 +0900 (JST) Message-ID: <20180604.132920.88922927875550163.t-is...@sraoss.co.jp> > Hi, > > PGconf.ASIA 2018 will be held on December 10 to 12 in Tokyo and we are > now accepting proposals for talks. > > Join us and other users and developers from around the world at the > home of the oldest and largest user group; Japan! > > (See http://www.pgconf.asia/EN/2017/program/ for last year's > conference program). > > - About the conference: > Location: Akihabara convention hall (Tokyo) > http://www.akibahall.jp/data/outline_eng.html > Targeted number of attendees: 700 in total > http://www.pgconf.asia/EN/2018/ > > - About the call for papers: > > - To submit a paper, please include the following details and send to: > pgconf-asia-2018-submission(at)pgconf(dot)asia > > Title > Abstract > Description > Language spoken during the talk: Japanese or English > Language of the talk material: Japanese and/or English > > - Submission deadline is midnight, 31st July, 2018 (Japan time). > > - Submissions should be sent in English, Japanese, or both. > Japanese-only submissions will be translated into English for > discussion within the program committee. > > - Presentation materials will be released the day after the conference > and will be made available to the public. The copyright of the > material will be retained by the author. > > We ask that you share your materials under a Creative Commons > license: https://creativecommons.org/licenses/by-nc-nd/4.0/ > > If your presentation material cannot be shared, please let us know. > > - Talks may
Re: Can't compile postgresql 11 on FreeBSD 11.1
On 17/07/2018 23:25, Tom Lane wrote: Adrian Klaver writes: On 07/17/2018 12:34 PM, Márcio Antônio Sepp wrote: I'm trying to compile PostgreSQL 11beta2 but this errors occur: checking readline.h usability... no Looks like you need whatever is the FreeBSD equivalent of readline-dev(el). AFAICT FreeBSD doesn't do things that way. On a nearby machine, I see $ pkg which /usr/local/include/readline/readline.h /usr/local/include/readline/readline.h was installed by package readline-6.3.8 What's more likely the problem is that FreeBSD insists on installing packages under /usr/local, but it does *not* set that up to be part of gcc's default search paths. (Security 1, usability 0.) You need these configure flags to do much of anything on that platform: --with-includes=/usr/local/include --with-libs=/usr/local/lib For ages I have been manually compiling PostgreSQL under FreeBSD with only : ./configure --with-ldap But in my case I tend to fall back to gcc instead of the native clang/llvm . That might be his issue. regards, tom lane -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
Re: Can't compile postgresql 11 on FreeBSD 11.1
On 23/07/2018 09:22, Achilleas Mantzios wrote: On 17/07/2018 23:25, Tom Lane wrote: Adrian Klaver writes: On 07/17/2018 12:34 PM, Márcio Antônio Sepp wrote: I'm trying to compile PostgreSQL 11beta2 but this errors occur: checking readline.h usability... no Looks like you need whatever is the FreeBSD equivalent of readline-dev(el). AFAICT FreeBSD doesn't do things that way. On a nearby machine, I see $ pkg which /usr/local/include/readline/readline.h /usr/local/include/readline/readline.h was installed by package readline-6.3.8 What's more likely the problem is that FreeBSD insists on installing packages under /usr/local, but it does *not* set that up to be part of gcc's default search paths. (Security 1, usability 0.) You need these configure flags to do much of anything on that platform: --with-includes=/usr/local/include --with-libs=/usr/local/lib For ages I have been manually compiling PostgreSQL under FreeBSD with only : ./configure --with-ldap But in my case I tend to fall back to gcc instead of the native clang/llvm . That might be his issue. It seems to be the case. With GCC installed, by default I get no problems : root@smadev:/usr/local/src/postgresql-11beta2# uname -a FreeBSD smadev.internal.net 11.1-RELEASE-p4 FreeBSD 11.1-RELEASE-p4 #0: Tue Nov 14 06:12:40 UTC 2017 r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC amd64 root@smadev:/usr/local/src/postgresql-11beta2# root@smadev:/usr/local/src/postgresql-11beta2# cd /usr/local/src/postgresql-11beta2 root@smadev:/usr/local/src/postgresql-11beta2# ./configure . config.status: linking src/makefiles/Makefile.freebsd to src/Makefile.port root@smadev:/usr/local/src/postgresql-11beta2# echo $? 0 By looking into config.log : configure:3885: checking for gcc configure:3901: found /usr/local/bin/gcc configure:3912: result: gcc configure:3943: checking for C compiler version configure:3952: gcc --version >&5 gcc (FreeBSD Ports Collection) 6.4.0 Now, if I configure for clang I get the error : root@smadev:/usr/local/src/postgresql-11beta2# setenv CC cc root@smadev:/usr/local/src/postgresql-11beta2# ./configure .. checking for readline/readline.h... no configure: error: readline header not found If you have libedit already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-readline to disable libedit support. root@smadev:/usr/local/src/postgresql-11beta2# As suggested, when using clang, the way to overcome is via : ./configure --with-includes=/usr/local/include --with-libs=/usr/local/lib regards, tom lane -- Achilleas Mantzios IT DEV Lead IT DEPT Dynacom Tankers Mgmt
