Re: [HACKERS] [POC] hash partitioning

2017-10-06 Thread Jesper Pedersen
Hi Amul, On 09/28/2017 05:56 AM, amul sul wrote: It does not really do the partition pruning via constraint exclusion and I don't think anyone is going to use the remainder in the where condition to fetch data and hash partitioning is not meant for that. But I am sure that we could solve this

[HACKERS] initdb w/ restart

2017-09-28 Thread Jesper Pedersen
cd7261877f160d1b0f7 Mon Sep 17 00:00:00 2001 From: jesperpedersen <jesper.peder...@redhat.com> Date: Thu, 28 Sep 2017 15:31:24 -0400 Subject: [PATCH] Change message for restarting a server from a directory without a PID file. This account for the case where a restart happens after an initdb

Re: [HACKERS] Partitions: \d vs \d+

2017-09-28 Thread Jesper Pedersen
On 09/28/2017 09:19 AM, Maksim Milyutin wrote: E.g. "No partition constraint" vs. "Partition constraint: satisfies_hash_partition(...)". I also noticed ambiguity in printing "No partition constraint" in non-verbose mode and "Partition constraint:..." in verbose one for partition tables

[HACKERS] Partitions: \d vs \d+

2017-09-28 Thread Jesper Pedersen
Hi, Using hash partitions I noticed that \d gives D=# \d T_p63 Table "public.T_p63" Column | Type | Collation | Nullable | Default ---+---+---+--+- Partition of: T FOR VALUES WITH (modulus 64, remainder 63) No

Re: [HACKERS] Multicolumn hash indexes

2017-09-27 Thread Jesper Pedersen
On 09/26/2017 08:11 PM, Robert Haas wrote: On Tue, Sep 26, 2017 at 7:18 PM, Tom Lane wrote: Tomasz Ostrowski writes: I've noticed that hash indexes can't currently (in PG10) be multicolumn. Are they technically hard to implement or just nobody took

Re: [HACKERS] [POC] hash partitioning

2017-09-27 Thread Jesper Pedersen
On 09/27/2017 03:05 AM, amul sul wrote: Attached rebased patch, thanks. While reading through the patch I thought it would be better to keep MODULUS and REMAINDER in caps, if CREATE TABLE was in caps too in order to highlight that these are "keywords" for hash partition. Also updated some of

Re: [HACKERS] path toward faster partition pruning

2017-09-26 Thread Jesper Pedersen
On 09/26/2017 10:33 AM, Robert Haas wrote: On Tue, Sep 26, 2017 at 9:00 AM, Jesper Pedersen <jesper.peder...@redhat.com> wrote: Could you share your thoughts on the usage of PartitionAppendInfo's min_datum_idx / max_datum_idx ? Especially in relation to hash partitions. This bri

Re: [HACKERS] Improve catcache/syscache performance.

2017-09-26 Thread Jesper Pedersen
On 09/26/2017 06:41 AM, tushar wrote: On 09/22/2017 11:45 AM, Andres Freund wrote: Here's a variant that cleans up the previous changes a bit, and adds some further improvements: I tested with different pgbench options with  master v/s patch and found an improvement.  I have applied 001 and

Re: [HACKERS] path toward faster partition pruning

2017-09-26 Thread Jesper Pedersen
Hi Amit, On 09/15/2017 04:50 AM, Amit Langote wrote: On 2017/09/15 11:16, Amit Langote wrote: I will post rebased patches later today, although I think the overall design of the patch on the planner side of things is not quite there yet. Of course, your and others' feedback is greatly welcome.

Re: [HACKERS] [POC] hash partitioning

2017-09-18 Thread Jesper Pedersen
On 09/15/2017 02:30 AM, amul sul wrote: Attached rebased patch, thanks. While reading through the patch I thought it would be better to keep MODULUS and REMAINDER in caps, if CREATE TABLE was in caps too in order to highlight that these are "keywords" for hash partition. Also updated some

Re: [HACKERS] [POC] hash partitioning

2017-09-14 Thread Jesper Pedersen
On 09/14/2017 01:52 PM, Robert Haas wrote: On Thu, Sep 14, 2017 at 1:07 PM, Jesper Pedersen <jesper.peder...@redhat.com> wrote: Yeah, it would be nice to have a syntax like ) PARTITION BY HASH (col) WITH (AUTO_CREATE = 64); But then there also needs to be a way to create the 64 asso

Re: [HACKERS] [POC] hash partitioning

2017-09-14 Thread Jesper Pedersen
On 09/14/2017 12:56 PM, Robert Haas wrote: On Thu, Sep 14, 2017 at 12:54 PM, David Fetter wrote: Should we be pointing the gun away from people's feet by making hash partitions that cover the space automagically when the partitioning scheme[1] is specified? In other words,

Re: [HACKERS] [POC] hash partitioning

2017-09-14 Thread Jesper Pedersen
Hi, On 09/14/2017 12:05 PM, Robert Haas wrote: On Thu, Sep 14, 2017 at 11:39 AM, Jesper Pedersen <jesper.peder...@redhat.com> wrote: When I do CREATE TABLE mytab ( a integer NOT NULL, b integer NOT NULL, c integer, d integer ) PARTITION BY HASH (b); and create 64 part

Re: [HACKERS] [POC] hash partitioning

2017-09-14 Thread Jesper Pedersen
Hi Amul, On 09/14/2017 04:58 AM, amul sul wrote: On Wed, Sep 13, 2017 at 7:43 PM, Jesper Pedersen <jesper.peder...@redhat.com This patch needs a rebase. Thanks for your note. ​ ​ Attached is the patch rebased on the latest master head. Also added error on ​creating ​​default partit

Re: [HACKERS] Fix performance degradation of contended LWLock on NUMA

2017-09-14 Thread Jesper Pedersen
On 09/11/2017 11:01 AM, Jesper Pedersen wrote: Thanks for working on this ! Moved to "Ready for Committer". Best regards,  Jesper -- 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] hash partitioning

2017-09-13 Thread Jesper Pedersen
Hi Amul, On 09/08/2017 08:40 AM, amul sul wrote: Rebased 0002 against this commit & renamed to 0001, PFA. This patch needs a rebase. Best regards, Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] POC: Cache data in GetSnapshotData()

2017-09-13 Thread Jesper Pedersen
Hi, On 08/29/2017 05:04 AM, Mithun Cy wrote: Test Setting: = Server configuration: ./postgres -c shared_buffers=8GB -N 300 -c min_wal_size=15GB -c max_wal_size=20GB -c checkpoint_timeout=900 -c maintenance_work_mem=1GB -c checkpoint_completion_target=0.9 -c wal_buffers=256MB & pgbench

Re: [HACKERS] Fix performance degradation of contended LWLock on NUMA

2017-09-11 Thread Jesper Pedersen
Hi, On 09/08/2017 03:35 PM, Sokolov Yura wrote: I'm seeing -M prepared: Up to 11% improvement -M prepared -S: No improvement, no regression ("noise") -M prepared -N: Up to 12% improvement for all runs the improvement shows up the closer you get to the number of CPU threads, or above. Although

Re: [HACKERS] Fix performance degradation of contended LWLock on NUMA

2017-09-08 Thread Jesper Pedersen
Hi, On 07/18/2017 01:20 PM, Sokolov Yura wrote: I'm sending rebased version with couple of one-line tweaks. (less skip_wait_list on shared lock, and don't update spin-stat on aquiring) I have been running this patch on a 2S/28C/56T/256Gb w/ 2 x RAID10 SSD setup (1 to 375 clients on logged

Re: [HACKERS] Fix performance of generic atomics

2017-09-06 Thread Jesper Pedersen
Hi Jeff, On 09/05/2017 03:47 PM, Jeff Janes wrote: I ran pgbench (-M prepared) with synchronous_commit 'on' and 'off' using both logged and unlogged tables. Also ran an internal benchmark which didn't show anything either. What scale factor and client count? How many cores per socket? It

Re: [HACKERS] Fix performance of generic atomics

2017-09-05 Thread Jesper Pedersen
Hi, On 05/25/2017 11:12 AM, Sokolov Yura wrote: I agree that lonely semicolon looks bad. Applied your suggestion for empty loop body (/* skip */). Patch in first letter had while(true), but I removed it cause I think it is uglier: - `while(true)` was necessary for grouping read with `if`, -

Re: [HACKERS] Fix performance of generic atomics

2017-09-05 Thread Jesper Pedersen
On 09/05/2017 02:24 PM, Tom Lane wrote: Jesper Pedersen <jesper.peder...@redhat.com> writes: I have tested this patch on a 2-socket machine, but don't see any performance change in the various runs. However, there is no regression either in all cases. Hm, so if we can't demon

Re: [HACKERS] Fix performance of generic atomics

2017-09-05 Thread Jesper Pedersen
Hi, On 05/25/2017 11:12 AM, Sokolov Yura wrote: I agree that lonely semicolon looks bad. Applied your suggestion for empty loop body (/* skip */). Patch in first letter had while(true), but I removed it cause I think it is uglier: - `while(true)` was necessary for grouping read with `if`, -

Re: [HACKERS] Page Scan Mode in Hash Index

2017-08-24 Thread Jesper Pedersen
On 08/24/2017 01:21 AM, Ashutosh Sharma wrote: Done. Attached are the patches with above changes. Thanks ! Based on the feedback in this thread, I have moved the patch to "Ready for Committer". Best regards, Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)

Re: [HACKERS] Page Scan Mode in Hash Index

2017-08-23 Thread Jesper Pedersen
On 08/23/2017 07:38 AM, Amit Kapila wrote: Thanks for the new version. I again looked at the patches and fixed quite a few comments in the code and ReadMe. You have forgotten to update README for the changes in vacuum patch (0003-Improve-locking-startegy-during-VACUUM-in-Hash-Index_v7). I

Re: [HACKERS] dtrace probes

2017-04-21 Thread Jesper Pedersen
On 04/20/2017 10:30 AM, Jesper Pedersen wrote: I think this fix is harmless and has some value in terms of consistency. One minor suggestion is that you should leave a space after typecasting. - TRACE_POSTGRESQL_LWLOCK_WAIT_DONE(T_NAME(lock), mode); + TRACE_POSTGRESQL_LWLOCK_WAIT_DONE(T_NAME

Re: [HACKERS] dtrace probes

2017-04-20 Thread Jesper Pedersen
esperpedersen <jesper.peder...@redhat.com> Date: Tue, 18 Apr 2017 11:44:18 -0400 Subject: [PATCH] Explicit cast LWLockMode, which an enum, to int in order to match the dtrace definition of the lwlock methods. Thereby all call sites will have the same definition instead of a mix between signed and unsign

[HACKERS] dtrace probes

2017-04-18 Thread Jesper Pedersen
e, 18 Apr 2017 11:44:18 -0400 Subject: [PATCH] Explicit cast LWLockMode, which an enum, to int in order to match the dtrace definition of the lwlock methods. Thereby all call sites will have the same definition instead of a mix between signed and unsigned. Author: Jesper Pedersen <jesper.peder..

Re: [HACKERS] bumping HASH_VERSION to 3

2017-03-31 Thread Jesper Pedersen
On 03/31/2017 02:17 PM, Robert Haas wrote: Starting a new thread about this to get more visibility. Despite the extensive work that has been done on hash indexes this release, we have thus far not made any change to the on-disk format that is not nominally backward-compatible. Commit

Re: [HACKERS] Page Scan Mode in Hash Index

2017-03-30 Thread Jesper Pedersen
Hi Ashutosh, On 03/29/2017 09:16 PM, Ashutosh Sharma wrote: This patch needs a rebase. Please try applying these patches on top of [1]. I think you should be able to apply it cleanly. Sorry, I think I forgot to mention this point in my earlier mail. [1] -

Re: [HACKERS] Page Scan Mode in Hash Index

2017-03-29 Thread Jesper Pedersen
Hi, On 03/27/2017 09:34 AM, Ashutosh Sharma wrote: Hi, I think you should consider refactoring this so that it doesn't need to use goto. Maybe move the while (offnum <= maxoff) logic into a helper function and have it return itemIndex. If itemIndex == 0, you can call it again. okay, Added

Re: [HACKERS] [POC] A better way to expand hash indexes.

2017-03-27 Thread Jesper Pedersen
Hi Mithun, On 03/26/2017 01:56 AM, Mithun Cy wrote: Thanks, Amit for the review. On Sat, Mar 25, 2017 at 7:03 PM, Amit Kapila wrote: I think one-dimensional patch has fewer places to touch, so that looks better to me. However, I think there is still hard coding and

Re: [HACKERS] Page Scan Mode in Hash Index

2017-03-23 Thread Jesper Pedersen
Hi, On 03/23/2017 02:11 PM, Ashutosh Sharma wrote: On Thu, Mar 23, 2017 at 8:29 PM, Jesper Pedersen <jesper.peder...@redhat.com> wrote: 0001v2: In hashgettuple() you can remove the 'currItem' and 'offnum' from the 'else' part, and do the assignment inside if (so->

Re: [HACKERS] Page Scan Mode in Hash Index

2017-03-23 Thread Jesper Pedersen
Hi, On 03/22/2017 09:32 AM, Ashutosh Sharma wrote: Done. Please refer to the attached v2 version of patch. Thanks. 1) 0001-Rewrite-hash-index-scans-to-work-a-page-at-a-time.patch: this patch rewrites the hash index scan module to work in page-at-a-time mode. It basically introduces two new

Re: [HACKERS] Page Scan Mode in Hash Index

2017-03-21 Thread Jesper Pedersen
Hi, On 02/14/2017 12:27 AM, Ashutosh Sharma wrote: Currently, Hash Index scan works tuple-at-a-time, i.e. for every qualifying tuple in a page, it acquires and releases the lock which eventually increases the lock/unlock traffic. For example, if an index page contains 100 qualified tuples, the

Re: [HACKERS] pageinspect: Hash index support

2017-02-03 Thread Jesper Pedersen
On 02/03/2017 11:41 AM, Jesper Pedersen wrote: contrib/pageinspect actually seems to lack *any* infrastructure for sharing functions across modules. It's time to remedy that. I propose inventing "pageinspect.h" to hold commonly visible declarations, and moving get_page_from_raw() into

Re: [HACKERS] pageinspect: Hash index support

2017-02-03 Thread Jesper Pedersen
Hi, On 02/03/2017 11:36 AM, Robert Haas wrote: On Fri, Feb 3, 2017 at 10:11 AM, Tom Lane wrote: BTW, the buildfarm is still crashing on ia64 and sparc64 members. I believe this is the same problem addressed in 84ad68d64 for pageinspect's GIN functions, to wit, the payload

Re: [HACKERS] pageinspect: Hash index support

2017-02-03 Thread Jesper Pedersen
On 02/02/2017 02:28 PM, Jesper Pedersen wrote: On 02/02/2017 02:24 PM, Robert Haas wrote: So, committed. Wow, I wish every patch had this many reviewers. Thanks Robert ! This message should have included a thank you to everybody who provided valuable feedback for this feature

Re: [HACKERS] pageinspect: Hash index support

2017-02-02 Thread Jesper Pedersen
On 02/02/2017 02:24 PM, Robert Haas wrote: So, committed. Wow, I wish every patch had this many reviewers. Thanks Robert ! Best regards, Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

Re: [HACKERS] Microvacuum support for Hash Index

2017-01-26 Thread Jesper Pedersen
On 01/23/2017 02:53 PM, Jesper Pedersen wrote: I have done some more testing with this, and have moved to the patch back to 'Needs Review' pending Amit's comments. Moved to "Ready for Committer". Best regards, Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@post

Re: [HACKERS] Microvacuum support for Hash Index

2017-01-23 Thread Jesper Pedersen
Hi Ashutosh, On 01/20/2017 03:24 PM, Jesper Pedersen wrote: Yeah, those are the steps; just with a Skylake laptop. However, I restarted with a fresh master, with WAL v8 and MV v5, and can't reproduce the issue. I have done some more testing with this, and have moved to the patch back

Re: [HACKERS] Microvacuum support for Hash Index

2017-01-20 Thread Jesper Pedersen
Hi Ashutosh, On 01/20/2017 04:18 AM, Ashutosh Sharma wrote: okay, Thanks for confirming that. I would like to update you that I am not able to reproduce this issue at my end. I suspect that the steps i am following might be slightly different than your's. Could you please have a look at steps

Re: [HACKERS] Microvacuum support for Hash Index

2017-01-19 Thread Jesper Pedersen
Hi Ashutosh, On 01/10/2017 08:40 AM, Jesper Pedersen wrote: On 01/10/2017 05:24 AM, Ashutosh Sharma wrote: Thanks for reporting this problem. It is basically coming because i forgot to unpin the bucketbuf in hash_xlog_vacuum_one_page(). Please find the attached v5 patch that fixes the issue

Re: [HACKERS] pageinspect: Hash index support

2017-01-18 Thread Jesper Pedersen
Best regards, Jesper >From 8e5a68cfaf979bcab88fb9358b88a89bc780a277 Mon Sep 17 00:00:00 2001 From: jesperpedersen <jesper.peder...@redhat.com> Date: Wed, 18 Jan 2017 08:42:02 -0500 Subject: [PATCH] Add support for hash index in pageinspect contrib module v15 Authors: Ashutosh Sharma and Jes

Re: [HACKERS] Write Ahead Logging for Hash Indexes

2017-01-12 Thread Jesper Pedersen
On 12/27/2016 01:58 AM, Amit Kapila wrote: After recent commit's 7819ba1e and 25216c98, this patch requires a rebase. Attached is the rebased patch. This needs a rebase after commit e898437. Best regards, Jesper -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To

Re: [HACKERS] pageinspect: Hash index support

2017-01-12 Thread Jesper Pedersen
at.com> Date: Thu, 12 Jan 2017 14:00:45 -0500 Subject: [PATCH] Add support for hash index in pageinspect contrib module v13 Authors: Ashutosh Sharma and Jesper Pedersen. --- contrib/pageinspect/Makefile | 10 +- contrib/pageinspect/hashfuncs.c

Re: [HACKERS] Retiring from the Core Team

2017-01-12 Thread Jesper Pedersen
On 01/11/2017 07:29 PM, Josh Berkus wrote: You will have noticed that I haven't been very active for the past year. My new work on Linux containers and Kubernetes has been even more absorbing than I anticipated, and I just haven't had a lot of time for PostgreSQL work. For that reason, as of

Re: [HACKERS] pageinspect: Hash index support

2017-01-10 Thread Jesper Pedersen
>From 2c75e4af17903c22961f23ed5455614340d0dd4e Mon Sep 17 00:00:00 2001 From: jesperpedersen <jesper.peder...@redhat.com> Date: Tue, 3 Jan 2017 07:49:42 -0500 Subject: [PATCH] Add support for hash index in pageinspect contrib module v11 Authors: Ashutosh Sharma and Jesper Pedersen. --- contrib/pagei

Re: [HACKERS] Microvacuum support for Hash Index

2017-01-10 Thread Jesper Pedersen
Hi Ashutosh, On 01/10/2017 05:24 AM, Ashutosh Sharma wrote: Thanks for reporting this problem. It is basically coming because i forgot to unpin the bucketbuf in hash_xlog_vacuum_one_page(). Please find the attached v5 patch that fixes the issue. The crash is now fixed, but the --- test.sql

Re: [HACKERS] Microvacuum support for Hash Index

2017-01-09 Thread Jesper Pedersen
Hi Ashutosh, On 01/06/2017 12:54 AM, Ashutosh Sharma wrote: using pgbench -M prepared -c 10 -j 10 -T 600 -f test.sql test crashes after a few minutes with TRAP: FailedAssertion("!(LWLockHeldByMeInMode(((LWLock*) (&(bufHdr)->content_lock)), LW_EXCLUSIVE))", File: "bufmgr.c", Line: 3781)

Re: [HACKERS] Microvacuum support for Hash Index

2017-01-05 Thread Jesper Pedersen
Hi Ashutosh, On 01/04/2017 06:13 AM, Ashutosh Sharma wrote: Attached is the v3 patch rebased on postgreSQL HEAD and WAL v7 patch. It also takes care of all the previous comments from Jesper - [1]. With an --enable-cassert build (master / WAL v7 / MV v3) and -- ddl.sql -- CREATE TABLE test

Re: [HACKERS] pageinspect: Hash index support

2017-01-05 Thread Jesper Pedersen
Hi Ashutosh, On 01/05/2017 07:13 AM, Ashutosh Sharma wrote: * Readded pageinspect--1.6.sql In order to have the latest pageinspect interface in 1 file, as we need something to install from. I think there should be no problem even if we simply add pageinspect--1.5--1.6.sql file instead of

Re: [HACKERS] pageinspect: Hash index support

2017-01-04 Thread Jesper Pedersen
can add for this. Best regards, Jesper >From 46374ba3aba57d290c1940fee4a12ed31c12342f Mon Sep 17 00:00:00 2001 From: jesperpedersen <jesper.peder...@redhat.com> Date: Tue, 3 Jan 2017 07:49:42 -0500 Subject: [PATCH] Add support for hash index in pageinspect contrib module v10 Authors: A

Re: [HACKERS] Microvacuum support for Hash Index

2016-12-30 Thread Jesper Pedersen
On 11/11/2016 12:11 AM, Ashutosh Sharma wrote: Hi Jesper, Some initial comments. _hash_vacuum_one_page: + END_CRIT_SECTION(); + _hash_chgbufaccess(rel, metabuf, HASH_READ, HASH_NOLOCK); The _hash_chgbufaccess() needs a comment. You also need a place where you

Re: [HACKERS] pageinspect: Hash index support

2016-12-20 Thread Jesper Pedersen
Hi, On 12/20/2016 05:55 AM, Ashutosh Sharma wrote: Attached is the revised patch. Please have a look and let me know your feedback. I have also changed the status for this patch in the upcoming commitfest to "needs review". Thanks. I was planning to submit a new version next week for

Re: [HACKERS] pg_catversion builtin function

2016-12-14 Thread Jesper Pedersen
On 12/14/2016 08:52 AM, Robert Haas wrote: But I understand your concern, so "Rejected" is ok under https://commitfest.postgresql.org/12/906/ I have a better reason for rejecting this patch: we already have this feature. rhaas=# select catalog_version_no from pg_control_system();

Re: [HACKERS] pg_catversion builtin function

2016-12-14 Thread Jesper Pedersen
On 12/13/2016 10:33 AM, Tom Lane wrote: Jesper Pedersen <jesper.peder...@redhat.com> writes: Attached is a new builtin function that exposes the CATALOG_VERSION_NO constant under the pg_catversion() function, e.g. I'm pretty sure that we intentionally didn't expose that, reasoning that

Re: [HACKERS] Hash Indexes

2016-12-13 Thread Jesper Pedersen
On 12/11/2016 11:37 PM, Amit Kapila wrote: On Sun, Dec 11, 2016 at 11:54 AM, Amit Kapila wrote: On Wed, Dec 7, 2016 at 2:02 AM, Jeff Janes wrote: With above fixes, the test ran successfully for more than a day. There was a small typo in the

[HACKERS] pg_catversion builtin function

2016-12-13 Thread Jesper Pedersen
Hi Hackers, Attached is a new builtin function that exposes the CATALOG_VERSION_NO constant under the pg_catversion() function, e.g. test=# SELECT pg_catversion(); pg_catversion --- 201612121 (1 row) Although it mostly useful during the development cycle to verify if

Re: [HACKERS] Microvacuum support for Hash Index

2016-12-01 Thread Jesper Pedersen
On 11/11/2016 12:11 AM, Ashutosh Sharma wrote: Thanks for reviewing this patch. I would like to update you that this patch has got dependency on a patch for concurrent hash index and WAL log in hash index. So, till these two patches for hash index are not stable I won't be able to share you a

Re: [HACKERS] Cache Hash Index meta page.

2016-12-01 Thread Jesper Pedersen
On 09/28/2016 11:55 AM, Mithun Cy wrote: On Tue, Sep 27, 2016 at 1:53 AM, Jeff Janes wrote: > I think that this needs to be updated again for v8 of concurrent and v5 of wal Adding the rebased patch over [1] + [2] As the concurrent hash index patch was committed in

Re: [HACKERS] Microvacuum support for Hash Index

2016-11-03 Thread Jesper Pedersen
Hi, On 11/02/2016 01:38 AM, Ashutosh Sharma wrote: While replaying the delete/vacuum record on standby, it can conflict with some already running queries. Basically the replay can remove some row which can be visible on standby. You need to resolve conflicts similar to what we do in btree

Re: [HACKERS] pageinspect: Hash index support

2016-11-02 Thread Jesper Pedersen
On 11/01/2016 03:28 PM, Peter Eisentraut wrote: Ok, thanks for your feedback. Maybe "Returned with Feedback" is more appropriate, as there is still development left. I have applied the documentation change that introduced subsections, which seems quite useful independently. I have also

Re: [HACKERS] pageinspect: Hash index support

2016-10-03 Thread Jesper Pedersen
On 09/29/2016 04:02 PM, Peter Eisentraut wrote: On 9/29/16 4:00 PM, Peter Eisentraut wrote: Since the commit fest is drawing to a close, I'll set this patch as returned with feedback. Actually, the CF app informs me that moving to the next CF is more appropriate, so I have done that. Ok,

Re: [HACKERS] pageinspect: Hash index support

2016-09-29 Thread Jesper Pedersen
On 09/29/2016 11:58 AM, Peter Eisentraut wrote: On 9/27/16 10:10 AM, Jesper Pedersen wrote: contrib/pageinspect/pageinspect--1.5--1.6.sql | 59 contrib/pageinspect/pageinspect--1.5.sql | 279 -- contrib/pageinspect/pageinspect--1.6.sql | 346

Re: [HACKERS] Write Ahead Logging for Hash Indexes

2016-09-27 Thread Jesper Pedersen
On 09/25/2016 01:00 AM, Amit Kapila wrote: Attached patch fixes the problem, now we do perform full page writes for bitmap pages. Apart from that, I have rebased the patch based on latest concurrent index patch [1]. I have updated the README as well to reflect the WAL logging related

Re: [HACKERS] Hash Indexes

2016-09-27 Thread Jesper Pedersen
On 09/20/2016 09:02 AM, Amit Kapila wrote: On Fri, Sep 16, 2016 at 11:22 AM, Amit Kapila wrote: I do want to work on it, but it is always possible that due to some other work this might get delayed. Also, I think there is always a chance that while doing that work,

Re: [HACKERS] pageinspect: Hash index support

2016-09-27 Thread Jesper Pedersen
On 09/26/2016 10:45 PM, Peter Eisentraut wrote: On 9/26/16 1:39 PM, Jesper Pedersen wrote: Left as is, since BuildTupleFromCStrings() vs. xyzGetDatum() are equally readable in this case. But, I can change the patch if needed. The point is that to use BuildTupleFromCStrings() you need

Re: [HACKERS] pageinspect: Hash index support

2016-09-26 Thread Jesper Pedersen
On 09/23/2016 01:56 AM, Amit Kapila wrote: While looking at patch, I noticed below code which seems somewhat problematic: + stat->max_avail = BLCKSZ - (BLCKSZ - phdr->pd_special + SizeOfPageHeaderData); + + /* page type (flags) */ + if (opaque->hasho_flag & LH_META_PAGE) + stat->type = 'm'; +

Re: [HACKERS] pageinspect: Hash index support

2016-09-26 Thread Jesper Pedersen
Hi, On 09/23/2016 12:10 AM, Peter Eisentraut wrote: On 9/21/16 9:30 AM, Jesper Pedersen wrote: Attached is v5, which add basic page verification. There are still some things that I found that appear to follow the old style (btree) conventions instead the new style (brin, gin) conventions

Re: [HACKERS] pageinspect: Hash index support

2016-09-21 Thread Jesper Pedersen
On 09/21/2016 08:43 AM, Michael Paquier wrote: page_stats / page_items should not be used on the metadata page. As these functions are marked as superuser only it is expected that people provides the correct input, especially since the raw page structure is used as the input. btree functions

Re: [HACKERS] pageinspect: Hash index support

2016-09-21 Thread Jesper Pedersen
On 09/21/2016 02:14 AM, Michael Paquier wrote: Adjusted in v4. Thanks for the new version. Code/doc will need an update once the CHI patch goes in. If you are referring to the WAL support, I guess that this patch or the other patch could just rebase and adjust as needed. It is the main

Re: [HACKERS] pageinspect: Hash index support

2016-09-21 Thread Jesper Pedersen
Hi, On 09/21/2016 07:24 AM, Ashutosh Sharma wrote: The development branch is @ https://github.com/jesperpedersen/postgres/tree/pageinspect_hash I am working on centOS 7. I am still facing the issue when applying your patch using "git apply" command but if i use 'patch' command it works fine

Re: [HACKERS] Write Ahead Logging for Hash Indexes

2016-09-20 Thread Jesper Pedersen
On 09/16/2016 02:42 AM, Amit Kapila wrote: Okay, Thanks for pointing out the same. I have fixed it. Apart from that, I have changed _hash_alloc_buckets() to initialize the page instead of making it completely Zero because of problems discussed in another related thread [1]. I have also

Re: [HACKERS] pageinspect: Hash index support

2016-09-20 Thread Jesper Pedersen
Hi, On 09/20/2016 01:46 PM, Ashutosh Sharma wrote: I am getting a fatal error along with some warnings when trying to apply the v3 patch using "git apply" command. [ashu@localhost postgresql]$ git apply 0001-pageinspect-Hash-index-support_v3.patch

Re: [HACKERS] pageinspect: Hash index support

2016-09-20 Thread Jesper Pedersen
On 09/20/2016 12:45 PM, Jeff Janes wrote: Is the 2nd "1" in this call needed? SELECT * FROM hash_page_stats(get_raw_page('mytab_index', 1), 1) As far as I can tell, that argument is only used to stuff into the output field "blkno", it is not used to instruct the interpretation of the raw page

Re: [HACKERS] pageinspect: Hash index support

2016-09-20 Thread Jesper Pedersen
On 09/20/2016 03:19 AM, Michael Paquier wrote: You did not get right the comments from Alvaro upthread. The following functions are added with this patch: function hash_metap(text) function hash_metap_bytea(bytea) function hash_page_items(text,integer) function hash_page_items_bytea(bytea)

Re: [HACKERS] pageinspect: Hash index support

2016-09-19 Thread Jesper Pedersen
On 09/14/2016 04:21 PM, Jeff Janes wrote: I suggest that pageinspect functions are more convenient to use via the get_raw_page interface, that is, instead of reading the buffer themselves, the buffer is handed over from elsewhere and they receive it as bytea. This enables other use cases such

Re: [HACKERS] Hash Indexes

2016-09-16 Thread Jesper Pedersen
On 09/16/2016 03:18 AM, Amit Kapila wrote: Attached is a run with 1000 rows. I think 1000 is also less, you probably want to run it for 100,000 or more rows. I suspect that the reason why you are seeing the large difference between btree and hash index is that the range of values is narrow

Re: [HACKERS] Hash Indexes

2016-09-15 Thread Jesper Pedersen
On 09/15/2016 02:03 AM, Amit Kapila wrote: Same thing here - where the fields involving the hash index aren't updated. Do you mean that for such cases also you see 40-60% gain? No, UPDATEs are around 10-20% for our cases. I have done a run to look at the concurrency / TPS aspect of the

Re: [HACKERS] Hash Indexes

2016-09-14 Thread Jesper Pedersen
Hi, On 09/14/2016 07:24 AM, Amit Kapila wrote: On Wed, Sep 14, 2016 at 12:29 AM, Jesper Pedersen <jesper.peder...@redhat.com> wrote: On 09/13/2016 07:26 AM, Amit Kapila wrote: Attached, new version of patch which contains the fix for problem reported on write-ahead-log of hash index

Re: [HACKERS] Hash Indexes

2016-09-13 Thread Jesper Pedersen
On 09/13/2016 07:26 AM, Amit Kapila wrote: Attached, new version of patch which contains the fix for problem reported on write-ahead-log of hash index thread [1]. I have been testing patch in various scenarios, and it has a positive performance impact in some cases. This is especially seen

Re: [HACKERS] Write Ahead Logging for Hash Indexes

2016-09-13 Thread Jesper Pedersen
On 09/13/2016 07:41 AM, Amit Kapila wrote: README: +in_complete split flag. The reader algorithm works correctly, as it will scan What flag ? in-complete-split flag which indicates that split has to be finished for that particular bucket. The value of these flags are

Re: [HACKERS] Hash Indexes

2016-09-13 Thread Jesper Pedersen
On 09/12/2016 10:42 PM, Amit Kapila wrote: The following script hangs on idx_val creation - just with v5, WAL patch not applied. Are you sure it is actually hanging? I see 100% cpu for a few minutes but the index eventually completes ok for me (v5 patch applied to today's master). It

Re: [HACKERS] Write Ahead Logging for Hash Indexes

2016-09-12 Thread Jesper Pedersen
Hi, On 09/07/2016 05:58 AM, Amit Kapila wrote: Okay, I have fixed this issue as explained above. Apart from that, I have fixed another issue reported by Mark Kirkwood upthread and few other issues found during internal testing by Ashutosh Sharma. The locking issue reported by Mark and

Re: [HACKERS] Hash Indexes

2016-09-12 Thread Jesper Pedersen
On 09/01/2016 11:55 PM, Amit Kapila wrote: I have fixed all other issues you have raised. Updated patch is attached with this mail. The following script hangs on idx_val creation - just with v5, WAL patch not applied. Best regards, Jesper zero.sql Description: application/sql --

Re: [HACKERS] Cache Hash Index meta page.

2016-09-08 Thread Jesper Pedersen
On 09/05/2016 02:50 PM, Mithun Cy wrote: On Sep 2, 2016 7:38 PM, "Jesper Pedersen" <jesper.peder...@redhat.com> wrote: Could you provide a rebased patch based on Amit's v5 ? Please find the the patch, based on Amit's V5. I have fixed following things 1. now in "

Re: [HACKERS] Cache Hash Index meta page.

2016-09-02 Thread Jesper Pedersen
On 07/22/2016 06:02 AM, Mithun Cy wrote: I have created a patch to cache the meta page of Hash index in backend-private memory. This is to save reading the meta page buffer every time when we want to find the bucket page. In “_hash_first” call, we try to read meta page buffer twice just to make

Re: [HACKERS] Hash Indexes

2016-09-01 Thread Jesper Pedersen
On 08/05/2016 07:36 AM, Amit Kapila wrote: On Thu, Aug 4, 2016 at 8:02 PM, Mithun Cy wrote: I did some basic testing of same. In that I found one issue with cursor. Thanks for the testing. The reason for failure was that the patch didn't take into account the

[HACKERS] pageinspect: Hash index support

2016-08-30 Thread Jesper Pedersen
Hi, Attached is a patch that adds support for hash indexes in pageinspect. The functionality (hash_metap, hash_page_stats and hash_page_items) follows the B-tree functions. This patch will need an update once Amit's and Mithun's work on Concurrent Hash Indexes is committed to account for

Re: [HACKERS] Speedup twophase transactions

2016-05-20 Thread Jesper Pedersen
Hi, On 04/13/2016 10:31 AM, Stas Kelvich wrote: On 13 Apr 2016, at 01:04, Michael Paquier wrote: That's too late for 9.6 unfortunately, don't forget to add that in the next CF! Fixed patch attached. There already was infrastructure to skip currently held locks

Re: [HACKERS] Speedup twophase transactions

2016-04-08 Thread Jesper Pedersen
On 04/08/2016 02:37 PM, Robert Haas wrote: On Fri, Apr 8, 2016 at 8:49 AM, Jesper Pedersen <jesper.peder...@redhat.com> wrote: On 04/07/2016 02:29 AM, Michael Paquier wrote: So recovery is conflicting here. My guess is that this patch is missing some lock cleanup. With the test case at

Re: [HACKERS] Speedup twophase transactions

2016-04-08 Thread Jesper Pedersen
On 04/08/2016 02:42 PM, Robert Haas wrote: On Tue, Jan 26, 2016 at 7:43 AM, Stas Kelvich wrote: Hi, Thanks for reviews and commit! I apologize for being clueless here, but was this patch committed? It's still marked as "Needs Review" in the CommitFest application.

Re: [HACKERS] Speedup twophase transactions

2016-04-08 Thread Jesper Pedersen
On 04/07/2016 02:29 AM, Michael Paquier wrote: So recovery is conflicting here. My guess is that this patch is missing some lock cleanup. With the test case attached in my case the COMMIT PREPARED record does not even get replayed. Should we create an entry for the open item list [0] for

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

2016-04-04 Thread Jesper Pedersen
On 04/01/2016 04:39 PM, Andres Freund wrote: On April 1, 2016 10:25:51 PM GMT+02:00, Jesper Pedersen <jesper.peder...@redhat.com> wrote: Hi, On 03/31/2016 06:21 PM, Andres Freund wrote: On March 31, 2016 11:13:46 PM GMT+02:00, Jesper Pedersen <jesper.peder...@redhat.com> wrote:

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

2016-04-01 Thread Jesper Pedersen
Hi, On 03/31/2016 06:21 PM, Andres Freund wrote: On March 31, 2016 11:13:46 PM GMT+02:00, Jesper Pedersen <jesper.peder...@redhat.com> wrote: I can do a USE_CONTENT_LOCK run on 0003 if it is something for 9.6. Yes please. I think the lock variant is realistic, the lockless did isn't

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

2016-03-31 Thread Jesper Pedersen
Hi, On 03/30/2016 07:09 PM, Andres Freund wrote: Yes. That looks good. My testing shows that increasing the number of buffers can increase both throughput and reduce latency variance. The former is a smaller effect with one of the discussed patches applied, the latter seems to actually increase

Re: [HACKERS] Speedup twophase transactions

2016-03-30 Thread Jesper Pedersen
On 03/30/2016 09:19 AM, Stas Kelvich wrote: > +++ b/src/test/recovery/t/006_twophase.pl > @@ -0,0 +1,226 @@ > +# Checks for recovery_min_apply_delay > +use strict; > This description is wrong, this file has been copied from 005. Yep, done. > > +my $node_master = get_new_node("Candie"); > +my

Re: [HACKERS] Speedup twophase transactions

2016-03-21 Thread Jesper Pedersen
On 03/18/2016 12:50 PM, Stas Kelvich wrote: On 11 Mar 2016, at 19:41, Jesper Pedersen <jesper.peder...@redhat.com> wrote: Thanks for review, Jesper. Some comments: * The patch needs a rebase against the latest TwoPhaseFileHeader change Done. * Rework the check.sh script into a TA

Re: [HACKERS] Speed up Clog Access by increasing CLOG buffers

2016-03-19 Thread Jesper Pedersen
On 03/15/2016 01:17 AM, Amit Kapila wrote: I have updated the comments and changed the name of one of a variable from "all_trans_same_page" to "all_xact_same_page" as pointed out offlist by Alvaro. I have done a run, and don't see any regressions. Intel Xeon 28C/56T @ 2GHz w/ 256GB + 2 x

  1   2   >