[GENERAL] Not clear how to switch role without permitting switch back

2017-01-09 Thread Guyren Howe
For my Love Your Database Project: https://medium.com/@gisborne/love-your-database-lydb-23c69f480a1d#.8g1ezwx6r I’m trying to see how a typical web developer might use Postgres’ roles and row-level security to

Re: [GENERAL] Slow index scan - Pgsql 9.2

2017-01-09 Thread David G. Johnston
On Mon, Jan 9, 2017 at 8:05 PM, Patrick B wrote: > ​3,581​ individual pokes into the heap to confirm tuple visibility and >> apply the deleted filter - that could indeed take a while. >> David J. > > > I see.. The deleted column is: > > deleted boolean > > Should I

Re: [GENERAL] Slow index scan - Pgsql 9.2

2017-01-09 Thread Patrick B
> > ​3,581​ individual pokes into the heap to confirm tuple visibility and > apply the deleted filter - that could indeed take a while. > David J. I see.. The deleted column is: deleted boolean Should I create an index for that? How could I improve this query? Does it execute as slowly when

Re: [GENERAL] Slow index scan - Pgsql 9.2

2017-01-09 Thread David G. Johnston
On Mon, Jan 9, 2017 at 6:06 PM, Patrick B wrote: > *Explain Analyze:* > > CTE Scan on "query_p" "qp" (cost=0.01..1060.57 rows=1 width=8) (actual > time=4065.244..4065.246 rows=1 loops=1) > > CTE query_p > > -> Result (cost=0.00..0.01 rows=1 width=0) (actual

Re: [GENERAL] Slow index scan - Pgsql 9.2

2017-01-09 Thread David Rowley
On 10 January 2017 at 14:06, Patrick B wrote: > -> Index Scan using "clientid_customers" on "customers" "c" > (cost=0.00..1059.01 rows=607 width=0) (actual time=9.105..4063.728 rows=2513 > loops=1) > Index Cond: ("clientid" =

Re: [GENERAL] Slow index scan - Pgsql 9.2

2017-01-09 Thread Jan de Visser
> > Hi guys, > > I've got the following Query: > > WITH > >query_p AS ( > >SELECT CAST(6667176 AS > BIGINT) AS client_id), > > > > > clients AS ( > >SELECT >

[GENERAL] Slow index scan - Pgsql 9.2

2017-01-09 Thread Patrick B
Hi guys, I've got the following Query: WITH query_p AS ( SELECT CAST(6667176 AS BIGINT) AS client_id), clients AS ( SELECT

Re: [GENERAL] Matching indexe for timestamp

2017-01-09 Thread Vitaly Burovoy
On 1/9/17, Job wrote: > Hello, > > on this table: > > Table "public.gruorari_tmp" > Column | Type | > Modifiers >

Re: [GENERAL] Matching indexe for timestamp

2017-01-09 Thread David G. Johnston
On Mon, Jan 9, 2017 at 2:58 PM, Job wrote: > > But in the query planner, at that point, Postgresql 9.6.1 seems not to use > any index (single on dalle / alle field and combindex index on dalle+alle) > but it use seqscan: > > Seq Scan on gruorari_tmp (cost=0.00..5.90

Re: R: [GENERAL] Matching indexe for timestamp

2017-01-09 Thread Adrian Klaver
On 01/09/2017 03:38 PM, Job wrote: Please also reply to list. I do not have time at the moment to go through this, someone else on the list might. Hi Adrian, You are right; here is the query and the planner. I think indexes are not used at all! /F EXPLAIN ANALYZE select

Re: [GENERAL] Matching indexe for timestamp

2017-01-09 Thread Adrian Klaver
On 01/09/2017 01:58 PM, Job wrote: Hello, on this table: Table "public.gruorari_tmp" Column | Type | Modifiers ---++-- id

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

2017-01-09 Thread Kevin Grittner
On Mon, Jan 9, 2017 at 11:49 AM, Israel Brewster wrote: > [load of new data] > Limit (cost=354643835.82..354643835.83 rows=1 width=9) (actual > time=225998.319..225998.320 rows=1 loops=1) > [...] I ran the query again [...] > Limit (cost=354643835.82..354643835.83

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Tom DalPozzo
> > Hi, I had already read that doc but I can't answer clearly to my >> questions 2,4 and 5. >> > > The answer would seem to depend on what you consider 'a consistency state > position'. Is it possible to be more explicit about what you mean? > >> >> Hi, I meant a position such that, if you

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Tom DalPozzo
> > > Hi, > > so let's suppose that the WAL is: > > LSN 10: start transaction 123 > > LSN 11: update tuple 100 > >checkpoint position here (not a record but just for understanding) > > LSN 12: update tuple 100 > > LSN 13: update tuple 100 > > LSN 14: checkpoint record ( postion=11) > > LSN 15:

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Alvaro Herrera
Tom DalPozzo wrote: > Hi, > so let's suppose that the WAL is: > LSN 10: start transaction 123 > LSN 11: update tuple 100 >checkpoint position here (not a record but just for understanding) > LSN 12: update tuple 100 > LSN 13: update tuple 100 > LSN 14: checkpoint record ( postion=11) > LSN

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Tom DalPozzo
> > Whether any individual tuple in the data files is visible or not depends > not only on the data itself, but also on the commit status of the > transactions that created it (and deleted it, if any). Replaying WAL > also updates the commit status of transactions, so if you're in the > middle of

[GENERAL] Matching indexe for timestamp

2017-01-09 Thread Job
Hello, on this table: Table "public.gruorari_tmp" Column | Type |Modifiers ---++-- id|

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Alvaro Herrera
Tom DalPozzo wrote: > 2) I see that a checkpoint position can be right in the middle of a group > of records related to a transaction (in the example, transaction id 10684). > So a checkpoint position is NOT a consistency state point, right? > 4) If I'm right at 2) then, between the checkpoint

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Adrian Klaver
On 01/09/2017 01:10 PM, Tom DalPozzo wrote: Reread your original post and realized you where also asking about transaction consistency and WALs. The thumbnail version is that Postgres writes transactions to the WALs before they are written to the data files on

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Tom DalPozzo
> > Reread your original post and realized you where also asking about >> transaction consistency and WALs. The thumbnail version is that Postgres >> writes transactions to the WALs before they are written to the data files >> on disk. A checkpoint represents a point in the sequence when is is

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

2017-01-09 Thread Rémi Cura
Hey, I like your curiosity ! At the billion range, you __have__ to use pgpointcloud, pyramid raster solution (actually the more common way to perform this task) or another database (hello monetdb). Cheers, Rémi-C 2017-01-09 20:11 GMT+01:00 Jonathan Vanasco : > > On Jan 9,

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

2017-01-09 Thread Jonathan Vanasco
On Jan 9, 2017, at 12:49 PM, Israel Brewster wrote: > Planning time: 4.554 ms > Execution time: 225998.839 ms > (20 rows) > > So a little less than four minutes. Not bad (given the size of the database), > or so I thought. > > This morning (so a couple of days later) I ran the query again

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

2017-01-09 Thread Paul Ramsey
At BILLIONS, you're getting to a point where the point index is probably (a) very large and (b) very deep, so you might want to do something different with your data storage, like loading the data in spatially compact patches of several 10s of points. Then the index will float more nicely in

Re: [GENERAL] Why autvacuum is not started?

2017-01-09 Thread Jeff Janes
On Mon, Jan 9, 2017 at 8:45 AM, Edmundo Robles wrote: > I have running Postgresql 9.4 and... if i have a table with following > configuration: > autovacuum_vacuum_scale_factor=0.0, > autovacuum_analyze_scale_factor=0.0, > autovacuum_vacuum_threshold=1000, >

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

2017-01-09 Thread Israel Brewster
So just for interests sake, to kick things up a notch (and out of sheer morbid curiosity), I loaded a higher-resolution dataset (Elevation data for the state of Alaska, 2 arc second resolution, as opposed to 100 meter resolution before). Same structure/indexes and everything, just higher

Re: [GENERAL] Why autvacuum is not started?

2017-01-09 Thread Adrian Klaver
On 01/09/2017 08:45 AM, Edmundo Robles wrote: I have running Postgresql 9.4 and... if i have a table with following configuration: autovacuum_vacuum_scale_factor=0.0, autovacuum_analyze_scale_factor=0.0, autovacuum_vacuum_threshold=1000, autovacuum_analyze_threshold=1000,

[GENERAL] Why autvacuum is not started?

2017-01-09 Thread Edmundo Robles
I have running Postgresql 9.4 and... if i have a table with following configuration: autovacuum_vacuum_scale_factor=0.0, autovacuum_analyze_scale_factor=0.0, autovacuum_vacuum_threshold=1000, autovacuum_analyze_threshold=1000, autovacuum_enabled=true Why autovacuum is not started if the

Re: R: R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

2017-01-09 Thread Adrian Klaver
On 01/09/2017 01:33 AM, Job wrote: Hi guys, Really thank you. Thanks to your help i solved the problem. For the record which problem(s)?: 1) Your original function issue. 2) The stand alone query you showed later. 3) Both. As said by Adrian: Caveats, it is morning here and coffee is

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Adrian Klaver
On 01/09/2017 06:47 AM, Tom DalPozzo wrote: https://www.postgresql.org/docs/9.5/static/wal-internals.html "After a checkpoint has been made and the log flushed, the checkpoint's position is saved in the

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Adrian Klaver
On 01/09/2017 06:47 AM, Tom DalPozzo wrote: https://www.postgresql.org/docs/9.5/static/wal-internals.html "After a checkpoint has been made and the log flushed, the checkpoint's position is saved in the

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Tom DalPozzo
> > https://www.postgresql.org/docs/9.5/static/wal-internals.html >> > > "After a checkpoint has been made and the log flushed, the checkpoint's > position is saved in the file pg_control. Therefore, at the start of > recovery, the server first reads pg_control and then the checkpoint record; >

Re: [GENERAL] checkpoint clarifications needed

2017-01-09 Thread Adrian Klaver
On 01/09/2017 06:14 AM, Tom DalPozzo wrote: Hi, I need some clarifications about checkpoints. Below here a log from my standby server when started and then some parts of the interested WAL in the master's cluster obtained by pg_xlogdump. Just to have an example to talk on. 1) I see: "LOG:

Re: [GENERAL] Querying dead rows

2017-01-09 Thread Albe Laurenz
Rakesh Kumar wrote: > Is there a way to query dead rows (that is, rows which are dead and still not > cleaned up by Vacuum) > using SQL. I am asking this just as an academical question. Sort of. You can use heap_page_item_attrs() from the pageinspect contrib module to get at the data, but you

[GENERAL] checkpoint clarifications needed

2017-01-09 Thread Tom DalPozzo
Hi, I need some clarifications about checkpoints. Below here a log from my standby server when started and then some parts of the interested WAL in the master's cluster obtained by pg_xlogdump. Just to have an example to talk on. 1) I see: "LOG: redo starts at 1/F00A7448" . I was expecting a

R: R: [GENERAL] Postgres 9.6.1 big slowdown by upgrading 8.4.22

2017-01-09 Thread Job
Hi guys, Really thank you. Thanks to your help i solved the problem. As said by Adrian: >>Caveats, it is morning here and coffee is still brewing, but I am not >>following. The left join limits grulist.stato to NULL, 1, 2. Your first >>condition catches the 1 value. Should not the second