Postgres: Do non-selected rows affect performance?

Question

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).

Cade Roux · Accepted Answer · 2017-03-07 18:10:53Z

1

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.

edited Mar 7, 2017 at 18:10

answered Mar 7, 2017 at 17:46

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Tallboy Over a year ago

Wow interesting. So, I have a second index on this table for: [:priority, :id] which satisfies my ORDER BY priority ASC, id ASC. Can I also use a partial index for that too?

Cade Roux Over a year ago

Yup - see the example they give "Example 11-2. Setting up a Partial Index to Exclude Uninteresting Values"

Cade Roux Over a year ago

Every index added to a table will slow it down, so if you are querying the '0' rows for other purposes, that would likely slow down updates for no good reason. But if the '0' rows are hardly queried (i.e. history archive), you could MOVE most of your other indexes to filtered indexes.

wildplasser Over a year ago

@CadeRoux: could you please point your link to a current version of the docs? postgresql.org/docs/current/static/indexes-partial.html

Collectives™ on Stack Overflow

Postgres: Do non-selected rows affect performance?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related