How to select records by condition in postgresql , and from this set select records with the same value of some field?

There is a table with million records.

like so

select c.id, c.mobile_phone, c.last_name, c.email
from user c
group by c.id, c.mobile_phone, c.last_name, c.email
HAVING count(*) > 1;

takes minutes.

It is necessary to select records with the same mobile_phone, and from the resulting selection, select records that have the same Email.

for example

I done that:

select * from (select * from user where mobile_phone = '00222334422222') ou
where (select count(*) from user inr
       where inr.email = ou.email) > 1;

and so:

SELECT
    *,
    ROW_NUMBER() OVER (
        PARTITION BY email, mobile_phone
        ORDER BY email, mobile_phone) AS Row_Number
FROM (select * from user where mobile_phone = '2225776676788') as "c*";

for example

select * from employee where
        mobile_phone = '75777302722';

the fields shown in Fig. should be in the final data sample.

It's a version

select * from (select * from employee where
                                          mobile_phone = '75777302722') ou
where (select count(*) from employee inr
       where inr.email = ou.email) > 1;

This code does not work on some versions of postgresql.

PostgreSQL 14.7 - it works.

PostgreSQL 13.7 - doen't work.

I get rows with the same phone number, but I can't select only those rows (from this dataset) where the email is the same (at the same time, rows that have Email = null should not be included)

5 users have the same phone number.
Of these 5 users - 2 have the same Email - they should be included in the final data set.

Are there any ideas how to do this?

SELECT id, mobile_phone, last_name, email
FROM (
    SELECT *, row_number() OVER (PARTITION BY mobile_phone, email) AS rn
    FROM user ) user_rn
WHERE mobile_phone = '75777302722'
  AND rn = 2;

In the inner query the row_number() function assigns a number to each combination of mobile_phone, email and the outer query then selects those records with multiple hits (note that rows with rn = 3 and higher should not be included because rn = 2 already supplies the combination).

This solution does not use the count(*) function, which is notoriously slow. If performance is an issue, you should add an index on (mobile_phone, email). Any table with more than a few thousand records (or, more precisely: with more than a few physical pages for data storage) will benefit from appropriate indexes tuned to the typical queries.