Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

2017-03-01 Thread David G. Johnston
On Wed, Mar 1, 2017 at 5:24 PM, Jeff Janes wrote: > On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston < > david.g.johns...@gmail.com> wrote: > >> On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta > > wrote: >> >>> plain analyze >>> select

Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

2017-03-01 Thread Jeff Janes
On Wed, Mar 1, 2017 at 2:12 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta > wrote: > >> plain analyze >> select tmp_san_1.id >> from tmp_san_1 >>left join tmp_san_2 on tmp_san_1.text =

Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

2017-03-01 Thread Tom Lane
Stefan Andreatta writes: > The same anti-join using the text fields, however estimates just 1 > resulting row, while there are still of course 9,999 of them: > =# explain analyze > select tmp_san_1.id > from tmp_san_1 > left join tmp_san_2 on

Re: [PERFORM] Speeding up JSON + TSQUERY + GIN

2017-03-01 Thread Sven R. Kunze
On 28.02.2017 17:49, Jeff Janes wrote: Oh. In my hands, it works very well. I get 70 seconds to do the {age: 20} query from pure cold caches, versus 1.4 seconds from cold caches which was followed by pg_prewarm('docs','prefetch'). How much RAM do you have? Maybe you don't have enough to

Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

2017-03-01 Thread Stefan Andreatta
On 02.03.2017 02:06, Tom Lane wrote: Stefan Andreatta writes: The same anti-join using the text fields, however estimates just 1 resulting row, while there are still of course 9,999 of them: =# explain analyze select tmp_san_1.id from tmp_san_1

Re: [PERFORM] Speeding up JSON + TSQUERY + GIN

2017-03-01 Thread Jeff Janes
On Wed, Mar 1, 2017 at 6:02 AM, Sven R. Kunze wrote: > On 28.02.2017 17:49, Jeff Janes wrote: > > Oh. In my hands, it works very well. I get 70 seconds to do the {age: > 20} query from pure cold caches, versus 1.4 seconds from cold caches which > was followed by

[PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

2017-03-01 Thread Stefan Andreatta
Hello, I have encountered a strange problem when doing an anti-join with a very small table via a varchar or text field as opposed to an integer field. Postgres version is 9.5.3 I did some experiments to extract the problem in a simple form. FIrst generate two tables with a series of

Re: [PERFORM] anti-join with small table via text/varchar cannot estimate rows correctly

2017-03-01 Thread David G. Johnston
On Wed, Mar 1, 2017 at 3:00 PM, Stefan Andreatta wrote: > plain analyze > select tmp_san_1.id > from tmp_san_1 >left join tmp_san_2 on tmp_san_1.text = tmp_san_2.text > where tmp_san_2.id is null; > > ​Does it help if you check for "tmp_san_2.text