CODEWITHSUNDEEP
databasepostgresqlindexing
Tallboy
Tallboy

Reputation: 13437

Postgres: Do non-selected rows affect performance?

My main question is, in a single table, do the number of records NOT included in a WHERE clause affect query performance of SELECT, INSERT, and UPDATE?

Say I have a table with 20 million rows, and this table has an indexed error string column.

Pretend 19,950,000 of those records have 0 set for this column, and 50,000 have it set to NULL.

My query does SELECT * FROM pending_emails WHERE error IS NULL.

After some logic in my app, I then need to update those same records by ID to set their error:

UPDATE "pending_emails" SET "error" = '0' WHERE "pending_emails"."id" = 46
UPDATE "pending_emails" SET "error" = '0' WHERE "pending_emails"."id" = 50

I'm trying to determine if I can leave 'completed' records in the database without affecting performance of the active records I'm working with, or if I should delete them (not preferred).

Upvotes: 1

Views: 91

Answers (1)

Cade Roux
Cade Roux

Reputation: 89681

Typically no. That's the purpose of indexing. You might want to consider a filtered index for this column: https://www.postgresql.org/docs/current/static/indexes-partial.html Then your index isn't even indexing the '0' rows at all.

Upvotes: 1

Related Questions