Re: SQL performance

On 2 June 2013 21:39, Robert DiFalco <robert(dot)difalco(at)gmail(dot)com> wrote:

> I have a table called contacts. It has a BIGINT owner_id which references
> a record in the user table. It also has a BIGINT user_id which may be null.
> Additionally it has a BOOLEAN blocked column to indicate if a contact is
> blocked. The final detail is that multiple contacts for an owner may
> reference the same user.
>
> I have a query to get all the user_ids of a non-blocked contact that is a
> mutual contact of the user. The important part of the table looks like this:
>
> CREATE TABLE contacts
> (
> id BIGINT PRIMARY KEY NOT NULL, // generated
> blocked BOOL,
> owner_id BIGINT NOT NULL,
> user_id BIGINT,
> FOREIGN KEY ( owner_id ) REFERENCES app_users ( id ) ON DELETE CASCADE,
> FOREIGN KEY ( user_id ) REFERENCES app_users ( id ) ON DELETE SET NULL
> );
> CREATE INDEX idx_contact_owner ON contacts ( owner_id );
> CREATE INDEX idx_contact_mutual ON contacts ( owner_id, user_id ) WHERE
> user_id IS NOT NULL AND NOT blocked;
>
> The query looks like this:
>
> explain analyze verbose
> select c.user_id
> from contact_entity c
> where c.owner_id=24 and c.user_id<>24 and c.user_id IS NOT NULL and NOT
> c.blocked and (exists (
> select 1
> from contact_entity c1
> where NOT c1.blocked and c1.owner_id=c.user_id and c1.user_id IS NOT
> NULL and c1.user_id=24))
> group by c.user_id;
>
> This will get all the users for user 24 that are mutual unblocked contacts
> but exclude the user 24.
>
> I have run this through explain several times and I'm out of ideas on the
> index. I note that I can also right the query like this:
>
> explain analyze verbose
> select distinct c.user_id
> from contact_entity c left outer join contact_entity c1 on c1.owner_id =
> c.user_id and c1.user_id = c.owner_id
> where NOT c.blocked AND NOT c1.blocked AND c.owner_id = 24 AND c.user_id
> <> 24
> AND c.user_id IS NOT NULL AND c1.user_id IS NOT NULL
> group by c.user_id;
>
> I don't notice a big difference in the query plans. I also notice no
> difference if I replace the GROUP BY with DISTINCT.
>
> My question is, can this be tightened further in a way I haven't been
> creative enough to try? Does it matter if I use the EXISTS versus the OUTER
> JOIN or the GROUP BY versus the DISTINCT.
>
> Is there a better index and I just have not been clever enough to come up
> with it yet? I've tried a bunch.
>
> Thanks in advance!!
>
> Robert
>

Hi Robert,
could you show us the plans?

thanks,
Szymon