date:20171105

On Sat, Oct 21, 2017 at 1:39 AM, Aleksandr Parfenov
 wrote:
> In attachment updated patch with fixes of empty XML tags in
> documentation.

Hi Aleksandr,

I'm not sure if this is expected at this stage, but just in case you
aren't aware, with this version of the patch the binary upgrade test
in
src/bin/pg_dump/t/002_pg_dump.pl fails for me:

#   Failed test 'binary_upgrade: dumps ALTER TEXT SEARCH CONFIGURATION
dump_test.alt_ts_conf1 ...'
#   at t/002_pg_dump.pl line 6715.

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Add ALWAYS DEFERRED option for constraints

On Fri, Oct 20, 2017 at 9:05 AM, Nico Williams  wrote:
> Rebased (there were conflicts in the SGML files).

Hi Nico

FYI that version has some stray absolute paths in constraints.source:

-COPY COPY_TBL FROM '@abs_srcdir@/data/constro.data';
+COPY COPY_TBL FROM '/home/nico/ws/postgres/src/test/regress/data/constro.data';

-COPY COPY_TBL FROM '@abs_srcdir@/data/constrf.data';
+COPY COPY_TBL FROM '/home/nico/ws/postgres/src/test/regress/data/constrf.data';

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [Sender Address Forgery]Re: [HACKERS] path toward faster partition pruning

2017-11-05 Thread Amit Langote

On 2017/11/06 12:53, David Rowley wrote:
> On 3 November 2017 at 17:32, David Rowley  
> wrote:
>> 2. This code is way more complex than it needs to be.
>>
>> if (num_parts > 0)
>> {
>> int j;
>>
>> all_indexes = (int *) palloc(num_parts * sizeof(int));
>> j = 0;
>> if (min_part_idx >= 0 && max_part_idx >= 0)
>> {
>> for (i = min_part_idx; i <= max_part_idx; i++)
>> all_indexes[j++] = i;
>> }
>> if (!bms_is_empty(other_parts))
>> while ((i = bms_first_member(other_parts)) >= 0)
>> all_indexes[j++] = i;
>> if (j > 1)
>> qsort((void *) all_indexes, j, sizeof(int), intcmp);
>> }
>>
>> It looks like the min/max partition stuff is just complicating things
>> here. If you need to build this array of all_indexes[] anyway, I don't
>> quite understand the point of the min/max. It seems like min/max would
>> probably work a bit nicer if you didn't need the other_parts
>> BitmapSet, so I recommend just getting rid of min/max completely and
>> just have a BitmapSet with bit set for each partition's index you
>> need, you'd not need to go to the trouble of performing a qsort on an
>> array and you could get rid of quite a chunk of code too.
>>
>> The entire function would then not be much more complex than:
>>
>> partindexes = get_partitions_from_clauses(parent, partclauses);
>>
>> while ((i = bms_first_member(partindexes)) >= 0)
>> {
>> AppendRelInfo *appinfo = rel->part_appinfos[i];
>> result = lappend(result, appinfo);
>> }
>>
>> Then you can also get rid of your intcmp() function too.
> 
> I've read a bit more of the patch and I think even more now that the
> min/max stuff should be removed.

Oops, I didn't catch this email before sending my earlier reply.  Thanks
for the bms range patch.  Will reply to this shortly after reading your
patch and thinking on it a bit.

Thanks,
Amit




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [PATCH] Overestimated filter cost and its mitigation

On Mon, Sep 11, 2017 at 7:43 PM, Yuto Hayamizu  wrote:
> Suppose that there are three qual clauses in a scan node, current
> postgres estimates per-tuple cost of the filter to be:
>cost(A) + cost(B) + cost(C)
>
> And basic idea of the attached patch is:
>cost(A) + clauselist_selectivity({A}) * cost(B) +
> clauselist_selectivity({A, B}) * cost(C)

I am no planner expert and I haven't tested or studied the patch in
detail, but here is some feedback for what it's worth.

This idea seems to makes intuitive sense.  I see that you use
order_qual_clauses() to know what order they'll run in, so I'm
wondering if there is any reason we shouldn't do it up front and keep
it during path building, instead of running it again at plan creation
time.  Is there some way it could ever produce a different result at
the two times?  Why not also apply this logic to qpquals of joins,
foreign scans, subplans?  That is, instead of replacing cost_qual_eval
with this code for baserels, I wonder if we should teach
cost_qual_eval how to do this so those other users could also benefit
(having perhaps already ordered the qual clauses earlier).

This is one of those patches that risks having an impact on many query
plans.  Yikes.  Of all the regression test queries, only
updatable_views complained though, and that involves leakproof
functions.  I see that those get some kind of special treatment in
order_qual_clauses().

+ ordered_clauses = order_qual_clauses(root, rel->baserestrictinfo);
+ clause_list = ordered_clauses;

Is clause_list necessary?  Can't you just use ordered_clauses for the
outer and inner loops?

+ List *clause_list_for_sel = NULL;

The convention is to use NIL for empty lists (a nod to the Lisp
machine they prototyped this project on).

+ /* Make a temporary clause list for selectivity calcuation */

s/calcuation/calculation/

-- 
Thomas Munro
http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] PATCH: psql tab completion for SELECT

2017-11-05 Thread Edmund Horner

Hi pgsql-hackers,

Here's a little draft patch to add *some* tab completion ability for
SELECT in psql.  I have often missed the ability, especially with
invocations of utility functions.

It would be nice to be able to suggest column names from the relevant
tables in the query, but, as the SQL language puts the column list
before the FROM clause, we have to use columns from all tables; I'm
certainly not the first to be frustrated by this language feature, but
we can't do much about it.



What my patch does:

For a command line with the single word SELECT, column names and
function names will be used for completions of the next word.
Function names have a "(" suffix added, largely because it makes them
easier to distinguish in the completion list.

Only the first select-list item can be tab-completed; i.e. SELECT foo,
b won't find column bar.

Examples:

postgres=# select rel
relacl   relchecksrelhasindex
relhassubclass   (etc.)

postgres=# select str
string_to_array(  strip(strpos(



How it works:

The implementation uses a normal non-schema query, because columns
don't have schemas.

The eligible columns are normal, visible columns.

I have tried to filter out the various functions which aren't likely
to be directly used in queries.

The eligible functions are those which are:
  - visible
  - not aggregate or window functions
  - not RI_FKey_* functions
  - not support functions for types, aggregates, operators, languages,
casts, access methods.

Completions for FROM, WHERE, etc. still work, since the additional
completions are only used immediately after the single word SELECT.



Is this likely to be a useful addition to PostgreSQL?

If so, I'll try to get the patch to a good standard.  I am not aiming
for complete support for the SELECT grammar, but just the low-hanging
fruit.

I'm not aware of existing tests for psql tab completion.  But I ran
"make check" anyway, with no problems.

Some other questions about how it should be done:

  - Are my criteria for the columns and function names appropriate?

  - Should I try to use a SchemaQuery so that function schema names can be used?

  - Should I try to support simple cases of multiple columns?  (How?
We can use TailMatches1(",") but how do we tell we aren't into the
FROM-clause or later?)

  - How do we make it work with older servers, e.g. those that predate
some of the newer columns used in the function criteria?

Cheers,
Edmund Horner


psql-select-tab-completion-v1.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Setting pd_lower in GIN metapage

2017-11-05 Thread Amit Kapila

On Sat, Nov 4, 2017 at 2:03 AM, Tom Lane  wrote:
> Michael Paquier  writes:
>> On Fri, Nov 3, 2017 at 1:10 AM, Amit Kapila  wrote:
>>> On Fri, Nov 3, 2017 at 2:54 AM, Tom Lane  wrote:
 I've marked the CF entry closed.  However, I'm not sure if we're quite
 done with this thread.  Weren't we going to adjust nbtree and hash to
 be more aggressive about labeling their metapages as REGBUF_STANDARD?
>
>>> I have already posted the patches [1] for the same in this thread and
>>> those are reviewed [2][3] as well. I have adjusted the comments as per
>>> latest commit.   Please find updated patches attached.
>
>> Confirmed. Setting those makes sense even if REGBUF_WILL_INIT is set,
>> at least for page masking.
>
> Thanks, I'd forgotten those patches were already posted.  Looks good,
> so pushed.
>
> Looking around, I noted that contrib/bloom also had the disease of
> not telling log_newpage it was writing a standard-format metapage,
> so I fixed that too.
>

Thanks, Michael and Tom for reviewing and committing the work.

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] path toward faster partition pruning

2017-11-05 Thread David Rowley

On 31 October 2017 at 21:43, Amit Langote  wrote:
> Attached updated version of the patches

match_clauses_to_partkey() needs to allow for the way quals on Bool
columns are represented.

create table pt (a bool not null) partition by list (a);
create table pt_true partition of pt for values in('t');
create table pt_false partition of pt for values in('f');
explain select * from pt where a = true;
QUERY PLAN
--
 Append  (cost=0.00..76.20 rows=2810 width=1)
   ->  Seq Scan on pt_false  (cost=0.00..38.10 rows=1405 width=1)
 Filter: a
   ->  Seq Scan on pt_true  (cost=0.00..38.10 rows=1405 width=1)
 Filter: a
(5 rows)

match_clause_to_indexcol() shows an example of how to handle this.

explain select * from pt where a = false;

will need to be allowed too. This works slightly differently.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] How to implement a SP-GiST index as a extension module?

2017-11-05 Thread Connor Wolf

Never mind, it turns out the issue boiled down to me declaring the
wrong prefixType in my config function.

TL;DR - PEBKAC

On Sun, Nov 5, 2017 at 1:09 AM, Connor Wolf  wrote:

> Ok, I've got everything compiling and it installs properly, but I'm
> running into problems that I think are either a side-effect of implementing
> picksplit incorrectly (likely), or a bug in SP-GiST(?).
>
> Program received signal SIGSEGV, Segmentation fault.
> __memcpy_sse2_unaligned () at ../sysdeps/x86_64/multiarch/
> memcpy-sse2-unaligned.S:159
> 159 ../sysdeps/x86_64/multiarch/memcpy-sse2-unaligned.S: No such file
> or directory.
> (gdb) bt
> #0  __memcpy_sse2_unaligned () at ../sysdeps/x86_64/multiarch/
> memcpy-sse2-unaligned.S:159
> #1  0x004ecd66 in memcpy (__len=16, __src=,
> __dest=0x13c9dd8) at /usr/include/x86_64-linux-gnu/bits/string3.h:53
> #2  memcpyDatum (target=target@entry=0x13c9dd8, att=att@entry=0x7fff327325f4,
> datum=datum@entry=18445692987396472528) at spgutils.c:587
> #3  0x004ee06b in spgFormInnerTuple (state=state@entry=0x7fff327325e0,
> hasPrefix=, prefix=18445692987396472528, nNodes=8,
> nodes=nodes@entry=0x13bd340) at spgutils.c:741
> #4  0x004f508b in doPickSplit (index=index@entry=0x7f2cf9de7f98,
> state=state@entry=0x7fff327325e0, current=current@entry=0x7fff32732020,
> parent=parent@entry=0x7fff32732040, 
> newLeafTuple=newLeafTuple@entry=0x13b9f00,
> level=level@entry=0, isNulls=0 '\000', isNew=0 '\000') at
> spgdoinsert.c:913
> #5  0x004f6976 in spgdoinsert (index=index@entry=0x7f2cf9de7f98,
> state=state@entry=0x7fff327325e0, heapPtr=heapPtr@entry=0x12e672c,
> datum=12598555199787281,
> isnull=0 '\000') at spgdoinsert.c:2053
> #6  0x004ee5cc in spgistBuildCallback 
> (index=index@entry=0x7f2cf9de7f98,
> htup=htup@entry=0x12e6728, values=values@entry=0x7fff327321e0,
> isnull=isnull@entry=0x7fff32732530 "", tupleIsAlive=tupleIsAlive@entry=1
> '\001', state=state@entry=0x7fff327325e0) at spginsert.c:56
> #7  0x00534e8d in IndexBuildHeapRangeScan
> (heapRelation=heapRelation@entry=0x7f2cf9ddc6c8,
> indexRelation=indexRelation@entry=0x7f2cf9de7f98,
> indexInfo=indexInfo@entry=0x1390ad8, allow_sync=allow_sync@entry=1
> '\001', anyvisible=anyvisible@entry=0 '\000', start_blockno=start_blockno@
> entry=0,
> numblocks=4294967295, callback=0x4ee573 ,
> callback_state=0x7fff327325e0) at index.c:2609
> #8  0x00534f52 in IndexBuildHeapScan 
> (heapRelation=heapRelation@entry=0x7f2cf9ddc6c8,
> indexRelation=indexRelation@entry=0x7f2cf9de7f98,
> indexInfo=indexInfo@entry=0x1390ad8, allow_sync=allow_sync@entry=1
> '\001', callback=callback@entry=0x4ee573 ,
> callback_state=callback_state@entry=0x7fff327325e0) at index.c:2182
> #9  0x004eeb74 in spgbuild (heap=0x7f2cf9ddc6c8,
> index=0x7f2cf9de7f98, indexInfo=0x1390ad8) at spginsert.c:140
> #10 0x00535e55 in index_build 
> (heapRelation=heapRelation@entry=0x7f2cf9ddc6c8,
> indexRelation=indexRelation@entry=0x7f2cf9de7f98,
> indexInfo=indexInfo@entry=0x1390ad8, isprimary=isprimary@entry=0
> '\000', isreindex=isreindex@entry=0 '\000') at index.c:2043
> #11 0x00536ee8 in index_create 
> (heapRelation=heapRelation@entry=0x7f2cf9ddc6c8,
> indexRelationName=indexRelationName@entry=0x12dd600 "int8idx_2",
> indexRelationId=16416, indexRelationId@entry=0, relFileNode=0,
> indexInfo=indexInfo@entry=0x1390ad8, indexColNames=indexColNames@
> entry=0x1390f40,
> accessMethodObjectId=4000, tableSpaceId=0,
> collationObjectId=0x12e6b18, classObjectId=0x12e6b38, coloptions=0x12e6b58,
> reloptions=0, isprimary=0 '\000',
> isconstraint=0 '\000', deferrable=0 '\000', initdeferred=0 '\000',
> allow_system_table_mods=0 '\000', skip_build=0 '\000', concurrent=0 '\000',
> is_internal=0 '\000', if_not_exists=0 '\000') at index.c:1116
> #12 0x005d8fe6 in DefineIndex (relationId=relationId@entry=16413,
> stmt=stmt@entry=0x12dd568, indexRelationId=indexRelationId@entry=0,
> is_alter_table=is_alter_table@entry=0 '\000',
> check_rights=check_rights@entry=1 '\001', check_not_in_use=check_not_in_
> use@entry=1 '\001', skip_build=0 '\000',
> quiet=0 '\000') at indexcmds.c:667
> #13 0x00782057 in ProcessUtilitySlow (pstate=pstate@entry=0x12dd450,
> pstmt=pstmt@entry=0x12db108,
> queryString=queryString@entry=0x12da0a0 "CREATE INDEX int8idx_2 ON
> int8tmp_2 USING spgist ( a vptree_ops );", context=context@entry=PROCESS_
> UTILITY_TOPLEVEL,
> params=params@entry=0x0, queryEnv=queryEnv@entry=0x0, dest=0x12db200,
> completionTag=0x7fff32732ed0 "") at utility.c:1326
> #14 0x007815ef in standard_ProcessUtility (pstmt=0x12db108,
> queryString=0x12da0a0 "CREATE INDEX int8idx_2 ON int8tmp_2 USING spgist ( a
> vptree_ops );",
> context=PROCESS_UTILITY_TOPLEVEL, params=0x0, queryEnv=0x0,
> dest=0x12db200, completionTag=0x7fff32732ed0 "") at utility.c:928
> #15 0x007816a7 in

Re: [HACKERS] path toward faster partition pruning

2017-11-05 Thread David Rowley

On 3 November 2017 at 17:32, David Rowley  wrote:
> 2. This code is way more complex than it needs to be.
>
> if (num_parts > 0)
> {
> int j;
>
> all_indexes = (int *) palloc(num_parts * sizeof(int));
> j = 0;
> if (min_part_idx >= 0 && max_part_idx >= 0)
> {
> for (i = min_part_idx; i <= max_part_idx; i++)
> all_indexes[j++] = i;
> }
> if (!bms_is_empty(other_parts))
> while ((i = bms_first_member(other_parts)) >= 0)
> all_indexes[j++] = i;
> if (j > 1)
> qsort((void *) all_indexes, j, sizeof(int), intcmp);
> }
>
> It looks like the min/max partition stuff is just complicating things
> here. If you need to build this array of all_indexes[] anyway, I don't
> quite understand the point of the min/max. It seems like min/max would
> probably work a bit nicer if you didn't need the other_parts
> BitmapSet, so I recommend just getting rid of min/max completely and
> just have a BitmapSet with bit set for each partition's index you
> need, you'd not need to go to the trouble of performing a qsort on an
> array and you could get rid of quite a chunk of code too.
>
> The entire function would then not be much more complex than:
>
> partindexes = get_partitions_from_clauses(parent, partclauses);
>
> while ((i = bms_first_member(partindexes)) >= 0)
> {
> AppendRelInfo *appinfo = rel->part_appinfos[i];
> result = lappend(result, appinfo);
> }
>
> Then you can also get rid of your intcmp() function too.

I've read a bit more of the patch and I think even more now that the
min/max stuff should be removed.

I understand that you'll be bsearching for a lower and upper bound for
cases like:

SELECT * FROM pt WHERE key BETWEEN 10 and 20;

but it looks like the min and max range stuff is thrown away if the
query is written as:

SELECT * FROM pt WHERE key BETWEEN 10 and 20 OR key BETWEEN 30 AND 40;

from reading the code, it seems like partset_union() would be called
in this case and if the min/max of each were consecutive then the
min/max range would get merged, but there's really a lot of code to
support this. I think it would be much better to invent
bms_add_range() and just use a Bitmapset to store the partition
indexes to scan. You could simply use bms_union for OR cases and
bms_intersect() or AND cases. It seems this would allow removal of
this complex code. It looks like this would allow you to remove all
the partset_range_* macros too.

I've attached a patch which implements bms_add_range() which will save
you from having to write the tight loops to call bms_add_member() such
as the ones in partset_union(). Those would not be so great if there
was a huge number of partitions as the Bitmapset->words[] array could
be expanded many more times than required. bms_add_range() will handle
that much more efficiently with a maximum of 1 repalloc() for the
whole operation. It would also likely faster since it's working at the
bitmapword level rather than bit level.

-- 
 David Rowley   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

0001-Add-bms_add_range-to-add-members-within-the-specifie.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Early locking option to parallel backup

Andres Freund  writes:
> Well, the current approach afaics requires #relations * 2 locks, whereas
> acquiring them in every worker would scale that with the number of
> workers.

Yeah, that's gonna be a problem with this proposal.

> IIUC the problem here is that even though a lock is already
> held by the main backend an independent locker's request will prevent
> the on-demand lock by the dump worker from being granted.  It seems to
> me the correct fix here would be to somehow avoid the fairness logic in
> the parallel dump case - although I don't quite know how to best do so.

I wonder if we couldn't somehow repurpose the work that was done for
parallel workers' locks.  Lots of security-type issues to be handled
if we're to open that up to clients, but maybe it's solvable.  For
instance, maybe only allowing it to clients sharing the same snapshot
would help.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] why not parallel seq scan for slow functions

Robert Haas  writes:
> This looks like it's on the right track to me.  I hope Tom will look
> into it, but if he doesn't I may try to get it committed myself.

I do plan to take a look at it during this CF.

> +/* Set or update cheapest_total_path and related fields */
> +set_cheapest(current_rel);

> I wonder if it's really OK to call set_cheapest() a second time for
> the same relation...

It's safe enough, we do it in some places already when converting
a relation to dummy.  But having to do that in a normal code path
suggests that something's not right about the design ...

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Restricting maximum keep segments by repslots

2017-11-05 Thread Craig Ringer

On 31 October 2017 at 17:43, Kyotaro HORIGUCHI
 wrote:
> Hello, this is a rebased version.
>
> It gets a change of the meaning of monitoring value along with
> rebasing.
>
> In previous version, the "live" column mysteriously predicts the
> necessary segments will be kept or lost by the next checkpoint
> and the "distance" offered a still more mysterious value.

Would it make sense to teach xlogreader how to fetch from WAL archive,
too? That way if there's an archive, slots could continue to be used
even after we purge from local pg_xlog, albeit at a performance cost.

I'm thinking of this mainly for logical slots.

-- 
 Craig Ringer   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Display number of heap accesses for index scans

Peter Geoghegan  writes:
> Andres Freund  wrote:
>> The number of index lookups that failed to return anything can be a
>> critical performance factor in OLTP workloads.  Therefore it seems like
>> it'd be a good idea to extend the explain analyze output to include that
>> information.

> I certainly agree.

Doesn't the EXPLAIN (BUFFERS) output already address this?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Custom compression methods

Robert Haas  writes:
> A basic problem here is that, as proposed, DROP COMPRESSION METHOD may
> break your database irretrievably.  If there's no data compressed
> using the compression method you dropped, everything is cool -
> otherwise everything is broken and there's no way to recover.  The
> only obvious alternative is to disallow DROP altogether (or make it
> not really DROP).

> Both of those alternatives sound fairly unpleasant to me, but I'm not
> exactly sure what to recommend in terms of how to make it better.
> Ideally anything we expose as an SQL command should have a DROP
> command that undoes whatever CREATE did and leaves the database in an
> intact state, but that seems hard to achieve in this case.

If the use of a compression method is tied to specific data types and/or
columns, then each of those could have a dependency on the compression
method, forcing a type or column drop if you did DROP COMPRESSION METHOD.
That would leave no reachable data using the removed compression method.
So that part doesn't seem unworkable on its face.

IIRC, the bigger concerns in the last discussion had to do with
replication, ie, can downstream servers make sense of the data.
Maybe that's not any worse than the issues you get with non-core
index AMs, but I'm not sure.

regards, tom lane

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] dropping partitioned tables without CASCADE

2017-11-05 Thread Amit Langote

On 2017/11/03 21:39, Alvaro Herrera wrote:
> Ashutosh Bapat wrote:
>> On Fri, Nov 3, 2017 at 1:42 PM, Alvaro Herrera  
>> wrote:
> 
>>> I think adding "is partitioned" at end of line isn't good; looks like a
>>> phrase but isn't translatable.  Maybe add keyword PARTITIONED instead?
>>
>> In that case may be we should separate bounds and "PARTITIONED" with a
>> ",". "part_default DEFAULT, PARTITIONED" would read better than
>> "part_default DEFAULT PARTITIONED"?
> 
> Hmm, I vote +0.5 for the comma.

Me too.

>>> Is it possible to put it at either start or end of the list?
>>
>> Right now, we could do that if we order the list by bound expression;
>> lexically DEFAULT would come before FOR VALUES ... . But that's not
>> future-safe; we may have a bound expression starting with A, B or C.
>> Beyond that it really gets tricky to order the partitions by bounds.
> 
> I was just thinking in changing the query to be "order by
> is_the_default_partition, partition_name" instead of just "order by
> partition_name".  Sorting by bounds rather than name (a feature whose
> worth should definitely be discussed separately IMV) sounds a lot more
> complicated.

Yeah, it sounds like a desirable feature, but as you both say, should be
discussed separately.  Since the facility to order partitions in the bound
order is internal to the server yet, we'd need some new server-side
functionality to expose the same with sane SQL-callable interface, which
clearly needs its own separate discussion.

Thanks,
Amit



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Secondary index access optimizations

On Fri, Sep 8, 2017 at 3:58 AM, Konstantin Knizhnik
 wrote:
> Updated version of the patch is attached to this mail.
> Also I added support of date type to operator_predicate_proof to be able to
> imply (logdate <= '2017-03-31') from (logdate < '2017-04-01') .

Hi Konstantin,

Is there any reason why you don't want to split this into two separate
proposals?  One for remove_restrictions_implied_by_constraints() and
one for the operator_predicate_proof() changes.

Your v3 patch breaks the new partition_join test (the recently
committed partition-wise join stuff), as far as I can tell in a good
way.  Can you please double check those changes and post an updated
patch?

-- 
Thomas Munro
http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Setting pd_lower in GIN metapage

2017-11-05 Thread Amit Langote

On 2017/11/03 6:24, Tom Lane wrote:
> Amit Langote  writes:
>> On 2017/09/26 16:30, Michael Paquier wrote:
>>> Cool, let's switch it back to a ready for committer status then.
> 
>> Sure, thanks.
> 
> Pushed with some cosmetic adjustments --- mostly, making the comments more
> explicit about why we need the apparently-redundant assignments to
> pd_lower.

Thank you.

Regards,
Amit



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Restricting maximum keep segments by repslots

On Tue, Oct 31, 2017 at 10:43 PM, Kyotaro HORIGUCHI
 wrote:
> Hello, this is a rebased version.

Hello Horiguchi-san,

I think the "ddl" test under contrib/test_decoding also needs to be
updated because it looks at pg_replication_slots and doesn't expect
your new columns.

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Custom compression methods

2017-11-05 Thread Adam Brusselback

> If there's no data compressed
> using the compression method you dropped, everything is cool -
> otherwise everything is broken and there's no way to recover.
> The only obvious alternative is to disallow DROP altogether (or make it
> not really DROP).

Wouldn't whatever was using the compression method have something
marking which method was used? If so, couldn't we just scan if there is
any data using it, and if so disallow the drop, or possibly an option to allow
the drop and rewrite the table either uncompressed, or with the default
compression method?


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Statement-level rollback

2017-11-05 Thread Tsunakawa, Takayuki

From: pgsql-hackers-ow...@postgresql.org
> [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Simon Riggs
> A backend-based solution is required for PL procedures and functions.
> 
> We could put this as an option into PL/pgSQL, but it seems like it is
> a function of the transaction manager rather than the driver.

Exactly.  Thanks.

Regards
Takayuki Tsunakawa



-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Early locking option to parallel backup

2017-11-05 Thread Andres Freund

On 2017-11-05 17:38:39 -0500, Robert Haas wrote:
> On Sun, Nov 5, 2017 at 5:17 AM, Lucas  wrote:
> > The patch creates a "--lock-early" option which will make pg_dump to issue
> > shared locks on all tables on the backup TOC on each parallel worker start.
> > That way, the backup has a very small chance of failing. When it does,
> > happen in the first few seconds of the backup job. My backup scripts (not
> > included here) are aware of that and retries the backup in case of failure.
> 
> I wonder why we don't do this already ... and by default.

Well, the current approach afaics requires #relations * 2 locks, whereas
acquiring them in every worker would scale that with the number of
workers.  IIUC the problem here is that even though a lock is already
held by the main backend an independent locker's request will prevent
the on-demand lock by the dump worker from being granted.  It seems to
me the correct fix here would be to somehow avoid the fairness logic in
the parallel dump case - although I don't quite know how to best do so.

Greetings,

Andres Freund

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Early locking option to parallel backup

On Sun, Nov 5, 2017 at 5:17 AM, Lucas  wrote:
> The patch creates a "--lock-early" option which will make pg_dump to issue
> shared locks on all tables on the backup TOC on each parallel worker start.
> That way, the backup has a very small chance of failing. When it does,
> happen in the first few seconds of the backup job. My backup scripts (not
> included here) are aware of that and retries the backup in case of failure.

I wonder why we don't do this already ... and by default.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Custom compression methods

On Sun, Nov 5, 2017 at 2:22 PM, Oleg Bartunov  wrote:
>> IIRC there were some concerns about what happened with pg_upgrade,
>> with consuming precious toast bits, and a few other things.
>
> yes, pg_upgrade may be a problem.

A basic problem here is that, as proposed, DROP COMPRESSION METHOD may
break your database irretrievably.  If there's no data compressed
using the compression method you dropped, everything is cool -
otherwise everything is broken and there's no way to recover.  The
only obvious alternative is to disallow DROP altogether (or make it
not really DROP).

Both of those alternatives sound fairly unpleasant to me, but I'm not
exactly sure what to recommend in terms of how to make it better.
Ideally anything we expose as an SQL command should have a DROP
command that undoes whatever CREATE did and leaves the database in an
intact state, but that seems hard to achieve in this case.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] why not parallel seq scan for slow functions

On Sun, Nov 5, 2017 at 12:57 AM, Amit Kapila  wrote:
> Thanks for the confirmation.  Find rebased patch attached.

This looks like it's on the right track to me.  I hope Tom will look
into it, but if he doesn't I may try to get it committed myself.

-if (rel->reloptkind == RELOPT_BASEREL)
-generate_gather_paths(root, rel);
+if (rel->reloptkind == RELOPT_BASEREL &&
+root->simple_rel_array_size > 2 &&
+!root->append_rel_list)

This test doesn't look correct to me.  Actually, it doesn't look
anywhere close to correct to me.  So, one of us is very confused...
not sure whether it's you or me.

 simple_gather_path = (Path *)
 create_gather_path(root, rel, cheapest_partial_path, rel->reltarget,
NULL, NULL);
+
+/* Add projection step if needed */
+if (target && simple_gather_path->pathtarget != target)
+simple_gather_path = apply_projection_to_path(root, rel,
simple_gather_path, target);

Instead of using apply_projection_to_path, why not pass the correct
reltarget to create_gather_path?

+/* Set or update cheapest_total_path and related fields */
+set_cheapest(current_rel);

I wonder if it's really OK to call set_cheapest() a second time for
the same relation...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [POC] Faster processing at Gather node

2017-11-05 Thread Andres Freund

Hi,

On November 5, 2017 1:33:24 PM PST, Jim Van Fleet  wrote:
>Ran this change with hammerdb  on a power 8 firestone
>
>with 2 socket, 20 core
>9.6 base--  451991 NOPM
>0926_master -- 464385 NOPM
>11_04master -- 449177 NOPM
>11_04_patch -- 431423 NOPM
>-- two socket patch is a little down from previous base runs
>
>With one socket
>9.6 base  -- 393727 NOPM 
>v10rc1_base -- 350958 NOPM
>11_04master -- 306506 NOPM
>11_04_patch -- 313179 NOPM
>--  one socket 11_04 master is quite a bit down from 9.6 and
>v10rc1_base 
>-- the patch is up a bit over the base
>
>Net -- the patch is about the same as current base on two socket, and
>on 
>one socket  -- consistent with your pgbench (?) findings
>
>As an aside, it is perhaps a worry that one socket is down over 20%
>from 
>9.6 and over 10% from v10rc1

What query(s) did you measure?

Andres
-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] [POC] Faster processing at Gather node

2017-11-05 Thread Jim Van Fleet

Ran this change with hammerdb  on a power 8 firestone

with 2 socket, 20 core
9.6 base--  451991 NOPM
0926_master -- 464385 NOPM
11_04master -- 449177 NOPM
11_04_patch -- 431423 NOPM
-- two socket patch is a little down from previous base runs

With one socket
9.6 base  -- 393727 NOPM 
v10rc1_base -- 350958 NOPM
11_04master -- 306506 NOPM
11_04_patch -- 313179 NOPM
--  one socket 11_04 master is quite a bit down from 9.6 and v10rc1_base 
-- the patch is up a bit over the base

Net -- the patch is about the same as current base on two socket, and on 
one socket  -- consistent with your pgbench (?) findings

As an aside, it is perhaps a worry that one socket is down over 20% from 
9.6 and over 10% from v10rc1

Jim

pgsql-hackers-ow...@postgresql.org wrote on 11/04/2017 06:08:31 AM:

> On hydra (PPC), these changes didn't help much.  Timings:
> 
> master: 29605.582, 29753.417, 30160.485
> patch: 28218.396, 27986.951, 26465.584
> 
> That's about a 5-6% improvement.  On my MacBook, though, the
> improvement was quite a bit more:
> 
> master: 21436.745, 20978.355, 19918.617
> patch: 15896.573, 15880.652, 15967.176
> 
> Median-to-median, that's about a 24% improvement.
> 
> Any reviews appreciated.
> 
> Thanks,
> 
> -- 
> Robert Haas
> EnterpriseDB: https://urldefense.proofpoint.com/v2/url?
> u=http-3A__www.enterprisedb.com=DwIBaQ=jf_iaSHvJObTbx-
> siA1ZOg=Glx_6-ZyGFPdLCdb8Jr7QJHrJIbUJO1z6oi-JHO8Htk=-
> 
I8r3tfguIVgEpNumrjWTKOGkJWIbHQNT2M2-02-8cU=39p2vefOiiZS9ZooPYkZ97U66hw5osqmkCGcikgZCik=
> The Enterprise PostgreSQL Company
> [attachment "shm-mq-less-spinlocks-v1.2.patch" deleted by Jim Van 
> Fleet/Austin/Contr/IBM] 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> https://urldefense.proofpoint.com/v2/url?
> 
u=http-3A__www.postgresql.org_mailpref_pgsql-2Dhackers=DwIDAg=jf_iaSHvJObTbx-
> siA1ZOg=Glx_6-ZyGFPdLCdb8Jr7QJHrJIbUJO1z6oi-JHO8Htk=-
> 
I8r3tfguIVgEpNumrjWTKOGkJWIbHQNT2M2-02-8cU=aL2TI3avKN4drlXk915UM2RFixyvUsZ2axDjB2FG9G0=

Re: [HACKERS] Display number of heap accesses for index scans

2017-11-05 Thread Peter Geoghegan

Andres Freund wrote:

The number of index lookups that failed to return anything can be a
critical performance factor in OLTP workloads. Therefore it seems like
it'd be a good idea to extend the explain analyze output to include that
information.

I certainly agree.

I've sometimes wondered if we could do better here by exploiting unique
indexes' uniqueness property. The general idea is that we could avoid
visiting duplicates in the heap when we've determined that they cannot
be visible to our MVCC snapshot - we already encountered the visible
version for that value, so this must be true. This is somewhat like what
we do with HOT chains, except it occurs across index tuples with the
same value rather than across heap tuples within a HOT chain.

The big difficulty is LP_DEAD bit setting within B-Tree pages; if no one
is visiting the heap, no one can opportunistically mark the bit to
indicate that nobody needs to visit the heap tuple pointed to by the
index tuple (because the heap tuple is dead to everyone). Now, there
isn't an immediate problem with that, because the main point of both
optimizations is to avoid heap visits. But there is a problem when there
needs to be a page split and there is a lack of LP_DEAD bits set from
which to reclaim space to defer the page split (in nbtree, we can
reclaim the space needed for our an index tuple that needs to be
inserted without actually spliting, in the end). We cannot undermine the
secondary goal of the LP_DEAD/kill_prior_tuple optimization, which is
reclaiming space early within B-Tree pages.

I can imagine a way of addressing this problem, though it is very
invasive - versioning distinct index tuple versions in the index with
ordinal version numbers. I wonder if anyone else has thought about doing
something like this, and has a better idea, though. I think that we
could be cleverer about unique indexes in a number of different ways
[1]. Reducing heap accesses is a big one.

[1]
https://wiki.postgresql.org/wiki/Key_normalization#Making_all_items_in_the_index_unique_by_treating_heap_TID_as_an_implicit_last_attribute
--
Peter Geoghegan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] WIP: long transactions on hot standby feedback replica / proof of concept

On Fri, Nov 3, 2017 at 5:57 PM, Alexander Korotkov
 wrote:
> On Thu, Nov 2, 2017 at 5:56 AM, Robert Haas  wrote:
>> > I can propose following alternative approach: teach read-only queries on
>> > hot
>> > standby to tolerate concurrent relation truncation.  Therefore, when
>> > non-existent heap page is accessed on hot standby, we can know that it
>> > was
>> > deleted by concurrent truncation and should be assumed to be empty.  Any
>> > thoughts?
>>
>> Sounds like it might break MVCC?
>
> I don't know why it might be broken.  VACUUM truncates heap only when tail
> to be truncated is already empty.  When applying truncate WAL record,
> previous WAL records deleting all those tuples in the tail are already
> applied.  Thus, if even MVCC is broken and we will miss some tuples after
> heap truncation, they were anyway were gone before heap truncation.

Ah - I was thinking of the TRUNCATE command, rather than truncation by
VACUUM.  Your argument makes sense, although the case where the
relation is truncated and later re-extended might need some thought.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Custom compression methods

2017-11-05 Thread Oleg Bartunov

On Thu, Nov 2, 2017 at 6:02 PM, Craig Ringer  wrote:
> On 2 November 2017 at 17:41, Ildus Kurbangaliev
>  wrote:
>
>> In this patch compression methods is suitable for MAIN and EXTENDED
>> storages like in current implementation in postgres. Just instead only
>> of LZ4 you can specify any other compression method.
>
> We've had this discussion before.
>
> Please read the "pluggable compression support" thread. See you in a
> few days ;) sorry, it's kinda long.
>
> https://www.postgresql.org/message-id/flat/20130621000900.GA12425%40alap2.anarazel.de#20130621000900.ga12...@alap2.anarazel.de
>

the proposed patch provides "pluggable" compression and let's user
decide by their own which algorithm to use.
The postgres core doesn't responsible for any patent problem.


> IIRC there were some concerns about what happened with pg_upgrade,
> with consuming precious toast bits, and a few other things.

yes, pg_upgrade may be a problem.

>
> --
>  Craig Ringer   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Small improvement to compactify_tuples

2017-11-05 Thread Claudio Freire

On Sat, Nov 4, 2017 at 8:07 PM, Юрий Соколов  wrote:
> 2017-11-03 5:46 GMT+03:00 Tom Lane :
>>
>> Sokolov Yura  writes:
>> > [ 0001-Improve-compactify_tuples.patch, v5 or thereabouts ]
>>
>> I went to check the shellsort algorithm against Wikipedia's entry,
>> and found that this appears to be an incorrect implementation of
>> shellsort: where pg_shell_sort_pass has
>>
>> for (_i = off; _i < _n; _i += off) \
>>
>> it seems to me that we need to have
>>
>> for (_i = off; _i < _n; _i += 1) \
>>
>> or maybe just _i++.
>
>
> Shame on me :-(
> I've wrote shell sort several times, so I forgot to recheck myself once
> again.
> And looks like best gap sequence from wikipedia is really best
> ( {301, 132, 57, 23, 10 , 4} in my notation),
>
>
> 2017-11-03 17:37 GMT+03:00 Claudio Freire :
>> On Thu, Nov 2, 2017 at 11:46 PM, Tom Lane  wrote:
>>> BTW, the originally given test case shows no measurable improvement
>>> on my box.
>>
>> I did manage to reproduce the original test and got a consistent
>> improvement.
>
> I've rechecked my self using my benchmark.
> Without memmove, compactify_tuples comsumes:
> - with qsort 11.66% cpu (pg_qsort + med3 + swapfunc + itemoffcompare +
> compactify_tuples = 5.97 + 0.51 + 2.87 + 1.88 + 0.44)
> - with just insertion sort 6.65% cpu (sort is inlined, itemoffcompare also
> inlined, so whole is compactify_tuples)
> - with just shell sort 5,98% cpu (sort is inlined again)
> - with bucket sort 1,76% cpu (sort_itemIds + compactify_tuples = 1.30 +
> 0.46)

Is that just insertion sort without bucket sort?

Because I think shell sort has little impact in your original patch
because it's rarely exercised. With bucket sort, most buckets are very
small, too small for shell sort to do any useful work.

That's why I'm inclined to agree with Tom in that we could safely
simplify it out, remove it, without much impact.

Maybe leave a fallback to qsort if some corner case produces big buckets?


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Re: [COMMITTERS] pgsql: Account for catalog snapshot in PGXACT->xmin updates.

2017-11-05 Thread Noah Misch

On Sat, Nov 04, 2017 at 12:23:36PM -0400, Tom Lane wrote:
> Noah Misch  writes:
> > I plan to use the attached patch after the minor release tags land.  If
> > there's significant support, I could instead push before the wrap.
> 
> This looks fine to me --- I think you should push now.

Done.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possible encoding issues with libxml2 functions

2017-11-05 Thread Pavel Stehule

Hi

2017-11-05 4:07 GMT+01:00 Noah Misch :

> On Tue, Oct 17, 2017 at 06:06:40AM +0200, Pavel Stehule wrote:
> > Please, if you can, try it write. I am little bit lost :)
>
> I'm attaching the patch I desired.  Please review.  This will probably miss
> this week's minor releases.  If there's significant support, I could
> instead
> push before the wrap.
>

I have not any objection to this solution. It fixes my regress tests too.

I checked it and it is working.

Regards

Pavel

Re: [HACKERS] Exclude pg_internal.init from base backup

2017-11-05 Thread Magnus Hagander

On Sat, Nov 4, 2017 at 4:04 AM, Michael Paquier 
wrote:

> On Fri, Nov 3, 2017 at 4:04 PM, Petr Jelinek
>  wrote:
> > Not specific problem to this patch, but I wonder if it should be made
> > more clear that those files (there are couple above of what you added)
> > are skipped no matter which directory they reside in.
>
> Agreed, it is a good idea to tell in the docs how this behaves. We
> could always change things so as the comparison is based on the full
> path like what is done for pg_control, but that does not seem worth
> complicating the code.
>

pg_internal.init can, and do, appear in multiple different directories.
pg_control is always in the same place. So they're not the same thing.

So +1 for documenting the difference in how these are handled, as this is
important to know for somebody writing an external tool for it.

It also seems the list in the documentation is not in sync with the code.
AFAICT docs are not mentioning the current_logfile. This seems to be a miss
in 19dc233c32f ?

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/

Re: [HACKERS] Minor comment issue in receivelog.c

2017-11-05 Thread Magnus Hagander

On Thu, Nov 2, 2017 at 5:18 PM, Bernd Helmle  wrote:

> Please find a minor comment fix for receivelog.c, HandleCopyStream().
>
> The comments talks about a START_STREAMING command, but i think
> START_REPLICATION is what's meant here.
>

Yeah, it is. Confusingly enough, START_STREAMING is what's returned by
START_REPLICATION.

Applied, thanks.

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/

Re: [HACKERS] Parallel Plans and Cost of non-filter functions

2017-11-05 Thread Paul Ramsey

On Sat, Nov 4, 2017 at 10:02 PM, Amit Kapila 
wrote:

> On Sat, Nov 4, 2017 at 4:43 AM, Tom Lane  wrote:
> > Paul Ramsey  writes:
> >>> Whether I get a parallel aggregate seems entirely determined by the
> number
> >>> of rows, not the cost of preparing those rows.
> >
> >> This is true, as far as I can tell and unfortunate. Feeding tables with
> >> 100ks of rows, I get parallel plans, feeding 10ks of rows, never do, no
> >> matter how costly the work going on within. That's true of changing
> costs
> >> on the subquery select list, and on the aggregate transfn.
> >
> > This sounds like it might be the same issue being discussed in
> >
> > https://www.postgresql.org/message-id/flat/CAMkU=
> 1ycXNipvhWuweUVpKuyu6SpNjF=yhwu4c4us5jgvgx...@mail.gmail.com
> >
>

Thanks Tom, Amit; yes, this issue (expensive things in target lists not
affecting plans) seems like what I'm talking about in this particular case
and something that shows up a lot in PostGIS use cases: a function on a
target list like ST_Buffer() or ST_Intersection() will be a couple orders
of magnitude more expensive than anything in the filters.


> I have rebased the patch being discussed on that thread.
>
> Paul, you might want to once check with the recent patch [1] posted on
> the thread mentioned by Tom.
>
> [1] - https://www.postgresql.org/message-id/CAA4eK1%2B1H5Urm0_
> Wp-n5XszdLX1YXBqS_zW0f-vvWKwdh3eCJA%40mail.gmail.com


Awesome! I will compare and report back,
Thanks much!
P



>
>
> --
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com
>

Re: [HACKERS] [POC] Faster processing at Gather node