Re: [GENERAL] DISTINCT vs GROUP BY - was Re: is (not) distinct from

2017-03-03 Thread George Neuner
Hi David, On Sat, 4 Mar 2017 02:32:48 +1300, David Rowley wrote: >On 3 March 2017 at 18:26, George Neuner wrote: >> I know most people here don't pay much - or any - attention to >> SQLServer, however there was an interesting article

Re: [GENERAL] Querying JSON Lists

2017-03-03 Thread Adrian Klaver
On 03/03/2017 11:50 AM, Sven R. Kunze wrote: On 03.03.2017 16:05, Adrian Klaver wrote: https://www.postgresql.org/docs/9.6/static/functions-json.html As to why it works on JSON arrays: Table 9-43. Additional jsonb Operators " ? text Does the string exist as a top-level key within the

Re: [GENERAL] Querying JSON Lists

2017-03-03 Thread Sven R. Kunze
On 03.03.2017 16:05, Adrian Klaver wrote: https://www.postgresql.org/docs/9.6/static/functions-json.html As to why it works on JSON arrays: Table 9-43. Additional jsonb Operators " ? text Does the string exist as a top-level key within the JSON value? " So to be picky it not does

Re: [GENERAL] odd optimizer result, index condition "is not null" on column defined as "not null"

2017-03-03 Thread Martin F
On 03/03/2017 17:33, Tom Lane wrote: Martin F writes: The select with filter choose an IMHO better plan Index Only Scan using tbl_foo_date on public.tbl_foo But the bigger picture here, which would become more obvious if you were working with a non-toy amount of data, is

Re: [GENERAL] odd optimizer result, index condition "is not null" on column defined as "not null"

2017-03-03 Thread Martin F
On 03/03/2017 17:33, Tom Lane wrote: Martin F writes: Index Cond: (tbl_foo.id IS NOT NULL) only "id" is the pk, and declared "not null". So why this index condition? You're right that we could observe that the NOT NULL is implied by a table constraint and drop it, but it

Re: [GENERAL] Understanding pg_last_xlog_receive_location

2017-03-03 Thread Zach Walton
Thanks Michael- That was indeed the issue. We have a very complex wrapper application that walks the server through multiple state transitions, and it turned out that in the state I was running the query from, streaming replication wasn't configured. On Wed, Mar 1, 2017 at 4:36 PM Michael Paquier

Re: [GENERAL] odd optimizer result, index condition "is not null" on column defined as "not null"

2017-03-03 Thread Martin Frb
Hi, following up my own post: I noted that I included the "set enable_seqscan=off; ". But the results I mentioned are from before this statement. I also compared some more statements explain analyze verbose select min(id) from tbl_foo where created_at >= '2017-01-15' ; explain analyze

Re: [GENERAL] odd optimizer result, index condition "is not null" on column defined as "not null"

2017-03-03 Thread Tom Lane
Martin F writes: >Index Cond: (tbl_foo.id IS NOT NULL) > only "id" is the pk, and declared "not null". > So why this index condition? The IS NOT NULL condition is generated as part of transforming a "min(x)" query into an indexscan, on the basis that "select min(x) from ..."

Re: [GENERAL] odd optimizer result, index condition "is not null" on column defined as "not null"

2017-03-03 Thread Martin F
Hi, following up my own post: I noted that I included the "set enable_seqscan=off; ". But the results I mentioned are from before this statement. I also compared some more statements explain analyze verbose select min(id) from tbl_foo where created_at >= '2017-01-15' ; explain analyze

[GENERAL] odd optimizer result, index condition "is not null" on column defined as "not null"

2017-03-03 Thread Martin F
Hi. I am new, and not sure which mailinglist this should go to, so I start with the general list. (please advice, if I should send this to a more specific list) This is tested with postgresql 9.5.5 (Maybe someone can confirm, if it is the same with later versions, saving me the work to

Re: [GENERAL] PortalSuspended

2017-03-03 Thread Tom Lane
Rui Pacheco writes: > Is there a way to force the backend to send a PortalSuspended message to > the front-end? In your Execute message, request fewer rows than you know the portal will produce. regards, tom lane -- Sent via pgsql-general

Re: [GENERAL] Querying JSON Lists

2017-03-03 Thread Adrian Klaver
On 03/02/2017 01:09 PM, Sven R. Kunze wrote: On 28.02.2017 17:33, Adrian Klaver wrote: On 02/26/2017 03:26 AM, Sven R. Kunze wrote: Hello everyone, playing around with jsonb and coming from this SO question

Re: [GENERAL] Autoanalyze oddity

2017-03-03 Thread Adrian Klaver
On 03/03/2017 12:33 AM, Peter J. Holzer wrote: This is with PostgreSQL 9.5.6 on Debian Linux. I noticed that according to pg_stat_user_tables autoanalyze has never run on a lot of tables. Here is one example: wdsah=> select * from pg_stat_user_tables where schemaname='public' and

Re: [GENERAL] Full Text Search combined with Fuzzy

2017-03-03 Thread Artur Zakirov
On 03.03.2017 16:17, Nicolas Paris wrote: Nice ! I do have 9.6 version. Would this kind of index could handle more than 20M large texts ? The recheck condition looks ressource consuming. You are right. I think pg_trgm will be not good for such large texts, unfortunately. The full text

Re: [GENERAL] DISTINCT vs GROUP BY - was Re: is (not) distinct from

2017-03-03 Thread David Rowley
On 3 March 2017 at 18:26, George Neuner wrote: > I know most people here don't pay much - or any - attention to > SQLServer, however there was an interesting article recently regarding > significant performance differences between DISTINCT and GROUP BY as > used to remove

Re: [GENERAL] Full Text Search combined with Fuzzy

2017-03-03 Thread Nicolas Paris
Le 03 mars 2017 à 14:08, Artur Zakirov écrivait : > On 03.03.2017 15:49, Nicolas Paris wrote: > > > >Hi Oleg, > > > >Thanks. I thought pgtrgm was not able to index my long texts because of > >limitation of 8191 bytes per index row for btree. > > > >Then I found out it is possible to use pgtrgm

Re: [GENERAL] Full Text Search combined with Fuzzy

2017-03-03 Thread Artur Zakirov
On 03.03.2017 15:49, Nicolas Paris wrote: Hi Oleg, Thanks. I thought pgtrgm was not able to index my long texts because of limitation of 8191 bytes per index row for btree. Then I found out it is possible to use pgtrgm over a GIN/GIST index. My final use case is phrase mining in texts. I

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Geoff Winkless
On 3 March 2017 at 12:17, Sven R. Kunze wrote: > On 03.03.2017 11:43, Geoff Winkless wrote: > > ​One alternative would be to make to_date accept all language variants of > months simultaneously. A quick search of google suggests that there aren't > any overlaps between languages

Re: [GENERAL] Full Text Search combined with Fuzzy

2017-03-03 Thread Nicolas Paris
Le 27 févr. 2017 à 10:32, Oleg Bartunov écrivait : > > > On Sun, Feb 26, 2017 at 3:52 PM, Nicolas Paris wrote: > > Hello, > > AFAIK there is no built-in way to combine full text search and fuzzy > matching >

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Sven R. Kunze
On 03.03.2017 11:43, Geoff Winkless wrote: ​One alternative would be to make to_date accept all language variants of months simultaneously. A quick search of google suggests that there aren't any overlaps between languages (ie where one language uses "Foo" for March and another uses "Foo" for

[GENERAL] PortalSuspended

2017-03-03 Thread Rui Pacheco
Hello, Is there a way to force the backend to send a PortalSuspended message to the front-end? Perhaps emulate a particular load or a specific sequence of sql commands sent from the front-end? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Sven R. Kunze
On 03.03.2017 11:43, Geoff Winkless wrote: One alternative would be to make to_date accept all language variants of months simultaneously. A quick search of google suggests that there aren't any overlaps between languages (ie where one language uses "Foo" for March and another uses "Foo" for

Re: [GENERAL] ERROR: functions in index expression must be marked IMMUTABLE

2017-03-03 Thread Geoff Winkless
On 1 March 2017 at 14:23, Sven R. Kunze wrote: > I don't consider rolling an UDF the best alternative especially after > having looked through many solution proposals on the Web which just take an > mutable expression and wrap them up in an immutable function. > ​One

Re: [GENERAL] DISTINCT vs GROUP BY - was Re: is (not) distinct from

2017-03-03 Thread Sven R. Kunze
On 03.03.2017 06:26, George Neuner wrote: I know most people here don't pay much - or any - attention to SQLServer, however there was an interesting article recently regarding significant performance differences between DISTINCT and GROUP BY as used to remove duplicates.

Re: [GENERAL] PG on SSD

2017-03-03 Thread Achilleas Mantzios
On 03/03/2017 01:31, Scott Marlowe wrote: On Thu, Mar 2, 2017 at 12:42 PM, scott ribe wrote: Is it reasonable to run PG on a mirrored pair of something like the Intel SSD DC 3610 series? (For example:

Re: [GENERAL] Querying JSON Lists

2017-03-03 Thread Sven R. Kunze
On 28.02.2017 17:33, Adrian Klaver wrote: On 02/26/2017 03:26 AM, Sven R. Kunze wrote: Hello everyone, playing around with jsonb and coming from this SO question http://stackoverflow.com/questions/19925641/check-if-a-postgres-json-array-contains-a-string I wonder why PostgreSQL behaves

[GENERAL] Autoanalyze oddity

2017-03-03 Thread Peter J. Holzer
This is with PostgreSQL 9.5.6 on Debian Linux. I noticed that according to pg_stat_user_tables autoanalyze has never run on a lot of tables. Here is one example: wdsah=> select * from pg_stat_user_tables where schemaname='public' and relname='facttable_wds_indexstats'; ─[ RECORD 1