-> Sort row IDs: users.userid  (actual time=24875.751..34999.822 rows=2547647 loops=1)
    -> Table scan on <temporary>  (cost=0.00..0.50 rows=6083517) (actual time=0.027..2488.740 rows=2547647 loops=1)
        -> Temporary table  (cost=7093350.93..7093351.43 rows=6083517) (actual time=21616.863..24280.942 rows=2547647 loops=1)
            -> Nested loop left join  (cost=6484999.19 rows=6083517) (actual time=0.127..12408.895 rows=2547647 loops=1)
                -> Nested loop left join  (cost=336301.25 rows=1446307) (actual time=0.113..5024.664 rows=2547647 loops=1)
                    -> Nested loop left join  (cost=188190.31 rows=1446307) (actual time=0.105..1015.506 rows=2547647 loops=1)
                        -> Nested loop left join  (cost=41177.15 rows=5475) (actual time=0.083..85.897 rows=7747 loops=1)
                            -> Nested loop inner join  (cost=38336.42 rows=5457) (actual time=0.069..43.167 rows=7747 loops=1)
                                -> Filter: ((CV.configuration_value = 1) and (CV.tenant_id is not null))  (cost=137.02 rows=53) (actual time=0.041..1.780 rows=3 loops=1)
                                    -> Index lookup on CV using X_tcv_tenant_configuration_id (tenant_configuration_id='236')  (cost=137.02 rows=527) (actual time=0.036..1.725 rows=527 loops=1)
                                -> Filter: ((users.grp = 3) and (users.`status` <> 8))  (cost=517.94 rows=104) (actual time=0.022..13.537 rows=2582 loops=3)
                                    -> Index lookup on users using X_cu_tenantid (tenantid=CV.tenant_id)  (cost=517.94 rows=2071) (actual time=0.017..11.156 rows=2859 loops=3)
                            -> Filter: (UEI.deleted = 0)  (cost=0.42 rows=1) (actual time=0.004..0.005 rows=1 loops=7747)
                                -> Index lookup on UEI using X_cuei_patient_id (patient_id=users.userid)  (cost=0.42 rows=1) (actual time=0.004..0.005 rows=1 loops=7747)
                        -> Covering index lookup on CV1 using U_tcv_tenant_configuration_id (tenant_id=CV.tenant_id)  (cost=0.44 rows=264) (actual time=0.016..0.095 rows=329 loops=7747)
                    -> Filter: (TC1.configuration_key in ('auxilary_physician_identifier_type','auxilary_nursing_agency_identifier_type'))  (cost=0.00 rows=1) (actual time=0.001..0.001 rows=0 loops=2547647)
                        -> Single-row index lookup on TC1 using PRIMARY (configuration_id=CV1.tenant_configuration_id)  (cost=0.00 rows=1) (actual time=0.001..0.001 rows=1 loops=2547647)
                -> Filter: (UEI.identity_value = up.patient_id)  (cost=3.83 rows=4) (actual time=0.003..0.003 rows=0 loops=2547647)
                    -> Index lookup on up using X_up_patient_id (patient_id=UEI.identity_value)  (cost=3.83 rows=4) (actual time=0.003..0.003 rows=0 loops=2547647)
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Commented Mar 22, 2023 at 12:38
  • With the query provided it would be easier to map. Is there a problem you wish to solve? Is 24 seconds too quick?
    – danblack
    Commented Mar 23, 2023 at 5:45
  • Please show us the query! And it may help to provide SHOW CREATE TABLE for each table.
    – Rick James
    Commented Apr 5, 2023 at 20:07


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.