Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 8:11 PM, Tom Lane wrote: > ​[*docs] > If the data were perfectly distributed, with the same > * number of tuples going into each available bucket, then the bucketsize > * fraction would be 1/nbuckets. But this happy state of affairs will > occur > * only if (a) there

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

"David G. Johnston" writes: > On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane wrote: >> The cost to form the inner hash is basically negligible whether it's >> de-duped or not, but if it's not (known) de-duped then the cost >> estimate for the semijoin is going to rise some, and that discourages >> sel

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 7:58 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane wrote: > >> >> The cost to form the inner hash is basically negligible whether it's >> de-duped or not, but if it's not (known) de-duped then the cost >> estimate fo

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:46 PM, Tom Lane wrote: > > The cost to form the inner hash is basically negligible whether it's > de-duped or not, but if it's not (known) de-duped then the cost > estimate for the semijoin is going to rise some, and that discourages > selecting it. > ​Why does the "has

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Jul 24, 2017 14:19, "PT" wrote: On Mon, 24 Jul 2017 13:17:56 +0300 Dmitry Lazurkin wrote: > On 07/24/2017 01:40 AM, PT wrote: > > In this example you count approximately 40,000,000 values, which is > > about 40% of the table. > > 4 000 000 (: > > > If you really need these queries to be fast

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

"David G. Johnston" writes: > On Mon, Jul 24, 2017 at 3:22 PM, Dmitry Lazurkin wrote: >> ALTER TABLE ids ALTER COLUMN id SET NOT NULL; >> EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id IN >> :values_clause; >> >> Aggregate (cost=245006.46..245006.47 rows=1 width=8) (actual >> time

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 01:25, David G. Johnston wrote: On Mon, Jul 24, 2017 at 3:22 PM, Dmitry Lazurkin >wrote: ALTER TABLE ids ALTER COLUMN id SET NOT NULL; EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id IN :values_clause; Aggregate (cost=245006

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 01:15, David G. Johnston wrote: On Mon, Jul 24, 2017 at 3:12 PM, Dmitry Lazurkin >wrote: And I have one question. I don't understand why IN-VALUES doesn't use Semi-Join? PostgreSQL has Hash Semi-Join... For which task the database has node of

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:22 PM, Dmitry Lazurkin wrote: > ALTER TABLE ids ALTER COLUMN id SET NOT NULL; > EXPLAIN (ANALYZE, BUFFERS) SELECT count(*) FROM ids WHERE id IN > :values_clause; > > Aggregate (cost=245006.46..245006.47 rows=1 width=8) (actual > time=3824.095..3824.095 rows=1 loops=1)

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, Jul 24, 2017 at 3:12 PM, Dmitry Lazurkin wrote: > And I have one question. I don't understand why IN-VALUES doesn't use > Semi-Join? PostgreSQL has Hash Semi-Join... For which task the database > has node of this type? > ​Semi-Join is canonically written as: SELECT * FROM tbl WHERE EXI

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 00:31, David G. Johnston wrote: Basically you want to write something like: SELECT * FROM ids JOIN ( :values_clause ) vc (vid) ON (vc.vid = ids.id )​ or WITH vc AS (SELECT vid FROM ORDER BY ... LIMIT ) SELECT * FROM ids JOIN vc ON (vid = ids.id

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 25.07.2017 00:17, PT wrote: The IN clause is not what's taking all the time. It's the processing of millions of rows that's taking all the time. IN (...) - 17 sec IN (VALUES ...) - 4 sec So performance issue is with IN-clause. Perhaps you should better describe what it is you really want t

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Sun, Jul 23, 2017 at 4:35 AM, dilaz03 . wrote: > - IN-VALUES clause adds new node to plan. Has additional node big > overhead? How about filter by two or more IN-VALUES clause? > ​IN-VALUES is just another word for "TABLE" which is another word for "RELATION". Writing relational database que

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On Mon, 24 Jul 2017 13:17:56 +0300 Dmitry Lazurkin wrote: > On 07/24/2017 01:40 AM, PT wrote: > > In this example you count approximately 40,000,000 values, which is > > about 40% of the table. > > 4 000 000 (: > > > If you really need these queries to be faster, I would suggest > > materializin

Re: [GENERAL] Monitoring of a hot standby with a largely idle master

On Sun, Jul 16, 2017 at 8:47 AM, Michael Paquier wrote: > On Fri, Jul 14, 2017 at 9:11 PM, Jeff Janes wrote: > > On Thu, Jul 13, 2017 at 10:38 AM, Michael Paquier > > wrote: > >> > >> On Thu, Jul 13, 2017 at 7:23 PM, Jeff Janes > wrote: > >> > > >> > I think that pg_stat_wal_receiver should be

Re: [GENERAL] pg_restore misuse or bug?

Assuming you have a user called "test", this will create a database that suffers from this problem. create database mvtest; \c mvtest create table main_table (id serial not null, something varchar(20), primary key (id)); create table child_table (id serial not null, parent_id int not null, somedat

Re: [GENERAL] monitoring PostgreSQL

On 24/07/17 15:01, PAWAN SHARMA wrote: > > > > On Mon, Jul 24, 2017 at 7:28 PM, Tim Clarke > wrote: > > > > On 24/07/17 14:47, PAWAN SHARMA wrote: > > > > Hi Tim, > > > > Facing below issue > > > > [abc@test:/home/psharm89/nrpe-2.15]# >

Re: [GENERAL] monitoring PostgreSQL

On Mon, Jul 24, 2017 at 7:28 PM, Tim Clarke wrote: > > > On 24/07/17 14:47, PAWAN SHARMA wrote: > > > > Hi Tim, > > > > Facing below issue > > > > [abc@test:/home/psharm89/nrpe-2.15]# > > Redirecting to /bin/systemctl restart xinetd.service > > Failed to restart xinetd.service: Unit xinetd.servi

Re: [GENERAL] monitoring PostgreSQL

On 24/07/17 14:47, PAWAN SHARMA wrote: > > Hi Tim, > > Facing below issue > > [abc@test:/home/psharm89/nrpe-2.15]# > Redirecting to /bin/systemctl restart xinetd.service > Failed to restart xinetd.service: Unit xinetd.service failed to load: > No such file or directory. > [abc@test:/home/psharm8

Re: [GENERAL] monitoring PostgreSQL

On Mon, Jul 24, 2017 at 5:50 PM, PAWAN SHARMA wrote: > > On Mon, Jul 24, 2017 at 5:20 PM, Tim Clarke > wrote: > >> We use nagios for all alerting, it'll do what you need. >> >> Tim Clarke >> >> >> On 24/07/17 12:38, Achilleas Mantzios wrote: >> > pgbadger is a very nice reporting tool, overall,

Re: [GENERAL] Dealing with ordered hierarchies

I don't like the approach with a large increment. It would mean complicated logic to see if you filled the gap and then update all the other peers if you did. It sounds like the re-order is going to be expensive no matter what. My primary concern are race conditions though. What if two or more user

Re: [GENERAL] monitoring PostgreSQL

On Mon, Jul 24, 2017 at 5:20 PM, Tim Clarke wrote: > We use nagios for all alerting, it'll do what you need. > > Tim Clarke > > > On 24/07/17 12:38, Achilleas Mantzios wrote: > > pgbadger is a very nice reporting tool, overall, albeit not exactly at > > the system side, but more to the DBA side.

Re: [GENERAL] monitoring PostgreSQL

We use nagios for all alerting, it'll do what you need. Tim Clarke On 24/07/17 12:38, Achilleas Mantzios wrote: > pgbadger is a very nice reporting tool, overall, albeit not exactly at > the system side, but more to the DBA side. > For system level monitoring maybe take a look here : > https://

Re: [GENERAL] monitoring PostgreSQL

pgbadger is a very nice reporting tool, overall, albeit not exactly at the system side, but more to the DBA side. For system level monitoring maybe take a look here : https://wiki.postgresql.org/wiki/Monitoring#check_postgres . Also you might want to write a script that parses logs for FATAL and

Re: [GENERAL] monitoring PostgreSQL

okmeter.io is good (though it's cloud-based and costs some money), I'm using it and definitely can recommend. There much more tools, see: https://wiki.postgresql.org/wiki/Monitoring On Mon, Jul 24, 2017 at 4:27 AM, PAWAN SHARMA wrote: > Hi All, > > > Please provide me a list of tools which we c

[GENERAL] monitoring PostgreSQL

Hi All, Please provide me a list of tools which we can use for monitoring PostgreSQL. -Monitor all the services and health of server -Able to send critical and warning alert on mail. OS: Redhat-7 PostgreSQL Version: 9.5.7 -Pawan

Re: [GENERAL] Dealing with ordered hierarchies

> On 24 Jul 2017, at 9:02, Tim Uckun wrote: > > I have read many articles about dealing with hierarchies in postgres > including nested sets, ltree, materialized paths, using arrays as parentage, > CTEs etc but nobody talks about the following scenario. > > Say I have a hierarchy like this >

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 07/24/2017 01:40 AM, PT wrote: > In this example you count approximately 40,000,000 values, which is > about 40% of the table. 4 000 000 (: > If you really need these queries to be faster, I would suggest > materializing the data, i.e. create a table like: > > CREATE TABLE id_counts ( > id BI

Re: [GENERAL] Perfomance of IN-clause with many elements and possible solutions

On 07/24/2017 01:40 AM, PT wrote: > In this example you count approximately 40,000,000 values, which is > about 40% of the table. 4 000 000 (: > If you really need these queries to be faster, I would suggest > materializing the data, i.e. create a table like: > > CREATE TABLE id_counts ( > id BI

Re: [GENERAL] Dealing with ordered hierarchies

On 24/07/2017 10:02, Tim Uckun wrote: I have read many articles about dealing with hierarchies in postgres including nested sets, ltree, materialized paths, using arrays as parentage, CTEs etc but nobody talks about the following scenario. Say I have a hierarchy like this 1 1.1 1.1.1 1.1.2 1

[GENERAL] Dealing with ordered hierarchies

I have read many articles about dealing with hierarchies in postgres including nested sets, ltree, materialized paths, using arrays as parentage, CTEs etc but nobody talks about the following scenario. Say I have a hierarchy like this 1 1.1 1.1.1 1.1.2 1.2 1.3 2 2.1 In this hierarchy the order