Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

2016-06-04 Thread Greg Navis
Thanks for your replies. Sorry for confusion. Instead of `similarity(lhs, rhs) >= show_limit()`, which of course is completely equivalent to `lhs % rhs`, I wanted to write `similarity(lhs, rhs) >= my_custom_threshold`. It seems that the approach with ternary operators is quite a bit of work. I mig

[GENERAL] Actual rows significantly more than estimated during many joins

2016-06-04 Thread Andrew Beverley
Dear all, I'm performing a query with many joins, with a WHERE condition on the "root" table. As far as I am aware, each join is indexed, as is the WHERE clause. To my simple mind, this is just a case of taking a set of conditional indexed values, and then "adding on" the relevant indexed data. W

Re: [GENERAL] Actual rows significantly more than estimated during many joins

2016-06-04 Thread Andrew Beverley
On Sat, 4 Jun 2016 Andrew Beverley wrote: > When I run EXPLAIN ANALYZE, I see that the actual query is scanning > significantly more rows for the join than was estimated. There is also > a huge number of loops for the joins. Why is this, and is there an > easy fix? I should have said: PostgreSQL

Re: [GENERAL] Replication

2016-06-04 Thread Vik Fearing
On 02/06/16 15:32, Bertrand Paquet wrote: > Hi, > > On an hot standby streaming server, is there any way to know, in SQL, to > know the ip of current master ? No. > The solution I have is to read the recovery.conf file to find > primary_conninfo, That is currently the only solution. There are

Re: [GENERAL] Replication

2016-06-04 Thread Vik Fearing
On 02/06/16 18:39, John R Pierce wrote: > On 6/2/2016 6:32 AM, Bertrand Paquet wrote: >> On an hot standby streaming server, is there any way to know, in SQL, >> to know the ip of current master ? >> The solution I have is to read the recovery.conf file to find >> primary_conninfo, but, it can be f

Re: [GENERAL] Partitioned tables do not return affected row counts to client

2016-06-04 Thread Vik Fearing
On 02/06/16 20:01, rverghese wrote: > We are looking to move from one large table to partitioned tables. Since the > inserts and updates are made to the master table and then inserted into the > appropriate partitioned table based on the trigger rules, the affected_rows > returned to the client (PH

Re: [GENERAL] WAL's listing in pg_xlog by some sql query

2016-06-04 Thread Vik Fearing
On 03/06/16 04:32, Michael Paquier wrote: > On Fri, Jun 3, 2016 at 11:23 AM, Sameer Kumar wrote: >> >> >> On Fri, Jun 3, 2016 at 4:30 AM Stephen Frost wrote: >>> >>> * Sameer Kumar (sameer.ku...@ashnik.com) wrote: On Fri, 3 Jun 2016, 12:14 a.m. Alex Ignatov, wrote: > Can I list all

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

2016-06-04 Thread Jeff Janes
On Sat, Jun 4, 2016 at 2:50 AM, Greg Navis wrote: > Thanks for your replies. > > Sorry for confusion. Instead of `similarity(lhs, rhs) >= show_limit()`, > which of course is completely equivalent to `lhs % rhs`, I wanted to write > `similarity(lhs, rhs) >= my_custom_threshold`. It seems that the a

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

2016-06-04 Thread Tom Lane
Jeff Janes writes: > I don't know if this would even be appropriate as an addition to > pg_trgm. We might want to fork that code instead. That would be a > shame, because the underlying c code would be the fundamentally the > same, but the alternative would be to force people who like % and > se

Re: [GENERAL] Actual rows significantly more than estimated during many joins

2016-06-04 Thread Andrew Beverley
On Sat, 4 Jun 2016 Andrew Beverley wrote: > I'm performing a query with many joins, with a WHERE condition on the > "root" table. As far as I am aware, each join is indexed, as is the > WHERE clause. To my simple mind, this is just a case of taking a set > of conditional indexed values, and then "