Menu

My "latest 20 issues" query read 200,000 rows, even with an index on project_id
📰

My "latest 20 issues" query read 200,000 rows, even with an index on project_id

DEV Community: postgres·Muhammad Adnan·6 days ago
#fIP5CXTr
#devtopostgresql#devto#dev#index#project
Reading 0:00
15s threshold

Almost every app has this query. Show the newest items in a project, one page at a time:

SELECT id , title , status , created_at FROM issues WHERE project_id = $ 1 ORDER BY created_at DESC , id DESC LIMIT 20 ;

Enter fullscreen mode

Exit fullscreen mode

And almost every schema has the index that seems to cover it:

CREATE INDEX ON issues ( project_id );

Enter fullscreen mode

Exit fullscreen mode

It's indexed, it's fast in development, and code review passes it. I measured what it does once one project gets big.

The setup

PostgreSQL 16, 2,000,000 issues across 2,000 projects. Project 1 is a big customer with 200,000 issues; project 500 is typical, with 901.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.