#4583 [BUG] Performance degradation with datacatalog.tags table Issue created by andrew-freenome ### Describe the bug I am seeing a performance bottleneck with the Flyte database. With my workload, the query

SELECT * FROM "tags" WHERE ("tags"."artifact_id","tags"."dataset_uuid") IN (($1,$2))

is getting executed frequently (380,000 times in the last day) against the

datacatalog

database. The workload I'm executing does have ~380k tasks, so the number of queries makes sense. On average, it takes 2 seconds to complete and returns 0 rows. I believe this query is executed as part of the task cache, which I do have enabled (but I expect all cache misses). I am using v1.0.1 of Datacatalog, and v1.1.47 of FlytePropeller. This is a database with 32 vCPU and 64GB of memory, 200GB storage with ~50GB of that used. [Screenshot 2023-12-05 at 12 36 55 PM](https://private-user-images.githubusercontent.com/153658602/289954686-4361b416-5ed0-422e-8a09-4085c2f15899.png?jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3NjA1MDg5MjMsIm5iZiI6MTc2MDUwODYyMywicGF0aCI6Ii8xNTM2NTg2MDIvMjg5OTU0Njg2LTQzNjFiNDE2LTVlZDAtNDIyZS04YTA5LTQwODVjMmYxNTg5OS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjUxMDE1JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI1MTAxNVQwNjEwMjNaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT0zNTEwNzU4ZjM2ZTc4MzZkYmYwOWM3MTljMWFkY2ZlZjA3NWYxZTIyZjM3MGNmNzljM2E5NjUwM2U5ODgwNDQ4JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCJ9.Ip0OI9maVKUKMZx40458bdTT6RTmHwRRmqxsQTM5K2A) ### Expected behavior I expect that the database would not be a performance bottleneck, and that the

datacatalog.tags

table is properly indexed in order to support the queries that are executed against it. ### Additional context to reproduce If I add an index (

CREATE INDEX tags_dataset_uuid_artifact_id_idx ON tags (dataset_uuid, artifact_id);

), the query gets significantly faster (1000x in my local testing). The postgres planner seems to vary the way it actually executes the query. The attached screenshots are from a different DB instance under slightly lesser load, but I'm adding them to show the 3 different ways I've seen the planner choose to execute the query. (Note that these are all without the index). Context: https://flyte-org.slack.com/archives/CP2HDHKE1/p1701797879836439 ### Screenshots [Screenshot 2023-12-06 at 2 03 33 PM](https://private-user-images.githubusercontent.com/153658602/289956786-28b63923-6b3c-4e79-a406-a3788618315a.png?jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3NjA1MDg5MjMsIm5iZiI6MTc2MDUwODYyMywicGF0aCI6Ii8xNTM2NTg2MDIvMjg5OTU2Nzg2LTI4YjYzOTIzLTZiM2MtNGU3OS1hNDA2LWEzNzg4NjE4MzE1YS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjUxMDE1JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI1MTAxNVQwNjEwMjNaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT02MDE4MTM4YTA4MTI1Y2FkN2Q0OTg4NTY1MTQyNzFhZmVjNTIxZTkxYTYzYjIyNmZiNjkwNzU2MDQzYTRmN2NhJlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCJ9.cVkTGF1H-GgGIe7H0QJM-9XJ4qzk07H1fvWvdxqN5Eo) [Screenshot 2023-12-06 at 2 03 45 PM](https://private-user-images.githubusercontent.com/153658602/289956789-b53c61e3-17ed-4ada-b045-6e68f83a6b68.png?jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3NjA1MDg5MjMsIm5iZiI6MTc2MDUwODYyMywicGF0aCI6Ii8xNTM2NTg2MDIvMjg5OTU2Nzg5LWI1M2M2MWUzLTE3ZWQtNGFkYS1iMDQ1LTZlNjhmODNhNmI2OC5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjUxMDE1JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI1MTAxNVQwNjEwMjNaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT0yNGYzYjA0Nzc1MTAwMjkwOGZhMGNlNTYyZGZjMWMzYzNmYTM4NDFjZGRkYzI2ZGY2Y2U1ZGRhNWUzNzJmODc2JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCJ9.fhA10poV9o52iytNTQS4eNWR8Ok11KgjODhzCiFIcqM) <https://private-user-images.githubusercontent.com/153658602/289956791-120dc17a-7839-4325-a00e-ebb517bd883a.png?jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3NjA1MDg5MjMsIm5iZiI6MTc2MDUwODYyMywicGF0aCI6Ii8xNTM2NTg2MDIvMjg5OTU2NzkxLTEyMGRjMTdhLTc4MzktNDMyNS1hMDBlLWViYjUxN2JkODgzYS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjUxMDE1JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI1MTAxNVQwNjEwMjNaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT02ZGEyNGEwMjhjOWM5MTRjNTJmY2NiNmY0M2VlYjMwY2IyZmYzOGJmYTAyOThmNWNhZWVkNzYwYTA5MTY0ZGE1JlgtQW16LVNpZ25lZEhlYWRlcnM9aG9zdCJ9.8Ub3h2rJopkA6XkZTtq-1LSVyj03l0C7W-NNX42DvzE|[Screenshot 2023-12-06 at 2 03 57 PM](https://private-user-images.githubusercontent.com/153658602/289956791-120dc17a-7839-4325-a00e-ebb517bd883a.png?jwt=eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3NjA1MDg5MjMsIm5iZiI6MTc2MDUwODYyMywicGF0aCI6Ii8xNTM2NTg2MDIvMjg5OTU2NzkxLTEyMGRjMTdhLTc4MzktNDMyNS1hMDBlLWViYjUxN2JkODgzYS5wbmc_WC1BbXotQWxnb3JpdGhtPUFXUzQtSE1BQy1TSEEyNTYmWC1BbXotQ3JlZGVudGlhbD1BS0lBVkNPRFlMU0E1M1BRSzRaQSUyRjIwMjUxMDE1JTJGdXMtZWFzdC0xJTJGczMlMkZhd3M0X3JlcXVlc3QmWC1BbXotRGF0ZT0yMDI1MTAxNVQwNjEwMjNaJlgtQW16LUV4cGlyZXM9MzAwJlgtQW16LVNpZ25hdHVyZT02ZGEyNGEwMjhjOWM5MTRjNTJmY2NiNmY0M2VlYjMwY2IyZmYzOGJ… flyteorg/flyte