How does this PostgreSQL query slow down when the number of rows increases?

Question

I have a table briefly structured like this:

tn( id integer NOT NULL primary key DEFAULT nextval('tn_sequence'),
                 create_dt TIMESTAMP NOT NULL DEFAULT NOW(),
                             ...............
                 deleted boolean );

create_dt is the timestamp when the row is inserted into the database.

deleted indicates that the row is or no longer useful.

And I have the following queries:

select * from tn where create_dt > ( NOW() - interval '150 seconds ) and deleted = FALSE;
select * from tn where create_dt < ( NOW() - interval '150 seconds ) and deleted = FALSE;

My question is how these query will slow down when the number of rows increase? For instance, when the number of rows exceeds 10K, 20K, or 100K, will it make a big impact on the speed? Is there any way I can optimize these queries? Note that every 5 seconds I will turn the column 'deleted' of rows which are older than 150 seconds into 'TRUE'.

primary key implies not null so the later is not necessary — Clodoaldo Neto
– Clodoaldo Neto, Commented Oct 7, 2012 at 10:12

Community · Accepted Answer · 2017-04-13 12:42:36Z

4

The effect of table growth on performance will depend on the query plan chosen, available indexes, the selectivity of the query, and lots of other factors. EXPLAIN ANALYZE on the query might help. In short, if your query only selects a few rows and can use a simple b-tree index then it won't usually slow down tons, only a little as the index grows. On the other hand queries using complex non-indexed conditions or returning lots of rows could perform very badly indeed.

Your issue appears to mirror that in the question How should we handle rows which won't be queried once they are old in PostgreSQL?

The advice given there should apply:

Use a partial index with the condition WHERE (not deleted); or
partition on 'deleted' with constraint exclusion enabled.

For example, you might:

CREATE INDEX create_dt_when_not_deleted_idx 
ON tn (create_dt)
WHERE (NOT deleted);

This includes only rows where deleted = 'f' (assuming deleted is `not null) in the index. This isn't the same as having them gone from the table completely.

Nothing changes with full table sequential scans, the deleted='t' rows must still be scanned; and
There's more I/O than if the deleted = 't' rows weren't there because any given heap page is likely to contain a mix of deleted = 't' and deleted = 'f' rows.

You can reduce the impact of the latter by CLUSTERing on an index that includes deleted. Again, this will have no effect on sequential scans. To help with sequential scans you would have to partition the table on deleted.

Pg 9.2's index only scans should (I think, haven't tested) use the partial index. When an index only scan is possible the partial index should be as fast as an index on a table containing only the deleted = 'f' rows.

Note that you'll need to keep table and index bloat under control. Ensure autovaccum runs very frequently and use a current version of PostgreSQL that doesn't need things like manually-managed free space map and has the latest, best-behaved autovacuum. I'd recommend 9.0 or above, preferably 9.1 or 9.2. Tune autovacuum to run aggressively.

When tuning and testing performance - test your queries with EXPLAIN ANALYZE, don't just guess.

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Oct 7, 2012 at 9:49

Craig Ringer

329k83 gold badges742 silver badges820 bronze badges

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

5 Comments

Craig Ringer Over a year ago

@MichaelKrelin-hacker Um, whoops. Fixed.

Michael Krelin - hacker Over a year ago

Sorry for keeping picking on you, but in the example you say when, not where ;-)

Craig Ringer Over a year ago

@MichaelKrelin-hacker No, thankyou. Clearly too tired to be writing answers. Fixed.

VB4EVA Over a year ago

@ Craig Ringer: thanks heaps Craig. So by adding this to the sql script. CREATE INDEX create_dt_when_not_deleted_idx ON tn (create_dt) WHERE (NOT deleted); then the create_dt will be partial indexed, and the row with 'deleted' = true wont be indexed, as a result wont be cared in the query?

Craig Ringer Over a year ago

@user1726452 That's a bit simplistic but mostly right. It means the rows with deleted = false won't affect the index. They're still there in the table, so they'll affect full table sequential scans and will increase I/O because any given heap page is likely to contain a mix of deleted = true and deleted = false rows. You can improve the latter problem by CLUSTERing the table on deleted or solve both by partitioning by deleted. In practice it might not matter - test your queries with explain analyze, don't just guess.

Collectives™ on Stack Overflow

How does this PostgreSQL query slow down when the number of rows increases?

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related