Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-12-01 Thread Tom Lane
George writes: > On Thu, Dec 1, 2016 at 6:58 PM, Tom Lane wrote: >> What >> do you get for >> select * from pg_stats where tablename = 'wg3ppbm_userpartner'; >> and likewise for wg3ppbm_partner? > It is a wide table. Do you want me to dump csv here?

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-12-01 Thread George
On Thu, Dec 1, 2016 at 6:58 PM, Tom Lane wrote: > George writes: >> explain analyze select * >> from wg3ppbm_transaction where partner_uuid in ( >> select p.uuid >> from wg3ppbm_userpartner up >> join wg3ppbm_partner

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-12-01 Thread Tom Lane
George writes: > explain analyze select * > from wg3ppbm_transaction where partner_uuid in ( > select p.uuid > from wg3ppbm_userpartner up > join wg3ppbm_partner p on p.id = up.partner_id > ); > "Hash Semi Join (cost=2.07..65628.14

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-12-01 Thread George
On Wed, Nov 30, 2016 at 10:08 PM, George wrote: > On Wed, Nov 30, 2016 at 8:44 PM, Tom Lane wrote: >> Merlin Moncure writes: >>> On Wed, Nov 30, 2016 at 11:05 AM, George wrote: So there is definitely

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread George
On Wed, Nov 30, 2016 at 8:44 PM, Tom Lane wrote: > Merlin Moncure writes: >> On Wed, Nov 30, 2016 at 11:05 AM, George wrote: >>> So there is definitely something wrong here. This situation makes many >>> row-level security use

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread Tom Lane
Merlin Moncure writes: > On Wed, Nov 30, 2016 at 11:05 AM, George wrote: >> So there is definitely something wrong here. This situation makes many >> row-level security use cases cumbersome since you need to have >> almost the same WHERE clause both in

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread Merlin Moncure
On Wed, Nov 30, 2016 at 11:05 AM, George wrote: > On Wed, Nov 30, 2016 at 6:45 PM, Merlin Moncure wrote: >> On Wed, Nov 30, 2016 at 10:42 AM, Tom Lane wrote: >>> George writes: explain select * from

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread George
On Wed, Nov 30, 2016 at 6:45 PM, Merlin Moncure wrote: > On Wed, Nov 30, 2016 at 10:42 AM, Tom Lane wrote: >> George writes: >>> explain select * from wg3ppbm_transaction where partner_uuid in ( >>> select p.uuid >>>

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread Merlin Moncure
On Wed, Nov 30, 2016 at 10:42 AM, Tom Lane wrote: > George writes: >> explain select * from wg3ppbm_transaction where partner_uuid in ( >> select p.uuid >> from wg3ppbm_userpartner up >> join wg3ppbm_partner p on

Re: [GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread Tom Lane
George writes: > explain select * from wg3ppbm_transaction where partner_uuid in ( > select p.uuid > from wg3ppbm_userpartner up > join wg3ppbm_partner p on p.id = up.partner_id > ); > "Hash Semi Join (cost=2.07..425.72 rows=2960

[GENERAL] Index is not used for "IN (non-correlated subquery)"

2016-11-30 Thread George
My use case: I have a table which I expect to reach a size of more than 10M rows. This table will have a column "partner_uuid" which will have a maximum envisioned cardinality of 10. I want different users of my web application to see different subsets of that table. I am using row-level