Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a fairly large table (25m entries) which stores tracking data, I am not a postgres admin by trade so this may be a simple problem.

these two queries although similar are taking massively different amounts of time and I am not sure why.

SELECT MAX(logdatetime) FROM tlog WHERE pointid = 'and.andrew' (5100ms)
SELECT MAX(logdatetime) FROM tlog WHERE pointid = '359710040139645' (41ms)

logdatetime is indexed, and I ran a VACUUM and REINDEX on the table when I noticed the discrepancy, but it hasn't resolved the issue.

Here is the EXPLAIN ANALYZE output for each of the queries:

--- and.andrew
Result  (cost=35.20..35.21 rows=1 width=0) (actual time=1924.811..1924.811 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..35.20 rows=1 width=8) (actual time=1924.804..1924.804 rows=1 loops=1)
          ->  Index Scan Backward using logdatetime on tlog  (cost=0.00..2434879.14 rows=69168 width=8) (actual time=1924.800..1924.800 rows=1 loops=1)
                Index Cond: (logdatetime IS NOT NULL)
                Filter: ((pointid)::text = 'and.andrew'::text)
Total runtime: 1924.853 ms

--- 359710040139645
Result  (cost=1.44..1.45 rows=1 width=0) (actual time=32.525..32.525 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.00..1.44 rows=1 width=8) (actual time=32.518..32.518 rows=1 loops=1)
          ->  Index Scan Backward using logdatetime on tlog  (cost=0.00..2434879.14 rows=1688018 width=8) (actual time=32.513..32.513 rows=1 loops=1)
                Index Cond: (logdatetime IS NOT NULL)
                Filter: ((pointid)::text = '359710040139645'::text)
Total runtime: 32.568 ms

The column pointid is defined as character varying (50), in case that matters.

share|improve this question

migrated from serverfault.com Oct 25 '12 at 23:25

This question came from our site for professional system and network administrators.

1  
It should not matter much, but is there a wildly different number of rows where pointid='and.andrew' versus the other? –  voretaq7 Oct 25 '12 at 23:24
2  
What version of PostgreSQL? –  Mike Sherrill 'Cat Recall' Oct 26 '12 at 1:13

1 Answer 1

up vote 4 down vote accepted

The output from EXPLAIN ANALYZE is very helpful for understanding this. In particular, note these two lines:

->  Index Scan Backward using logdatetime on tlog
    (cost=0.00..2434879.14 rows=69168 width=8)
    (actual time=1924.800..1924.800 rows=1 loops=1)

->  Index Scan Backward using logdatetime on tlog
    (cost=0.00..2434879.14 rows=1688018 width=8)
    (actual time=32.513..32.513 rows=1 loops=1)

I reformatted and added bold emphasis.

Since you only seem to have an index on logdatetime that's what the query planner is going to use. It seems promising that you only want a single row, with MAX(logdatetime), so the query planner thinks it's a good idea to start from the back of the index (Index Scan Backward) and scan until the first row with matching pointid pops up.

This strategy works out just nicely for pointid = '359710040139645', but not so much for pointid = 'and.andrew'. The maximum logdatetime for this particular pointid is obviously rather old, so a lot of rows (1.688.018) have to be read before the first hit (which is the youngest row).

But still a lot better than a full table scan where you would need to read all 25m rows to identify the matching ones, then find the max among these. (Try deleting the index an rerun the query. Be prepared for a long wait.)

If you can afford adding indexes (some cost to write operations and considerable disk space for 25m rows) try adding an index on pointid instead, or better yet, this multi-column index:

CREATE INDEX tlog_pointid_logdatetime ON tlog (pointid, logdatetime DESC);

This would make your query fly.

If you don't want indexes to slow down your writes, you might create this index just for the purpose of running a couple of queries and delete it again. Index creation is pretty fast in PostgreSQL. This may pay even for just a few queries.

share|improve this answer
2  
Thanks Erwin for taking the time to explain this in detail - the output now makes a lot more sense to me and I have always just created indexes for single columns but realise this is not optimal because 90% of the time they are grouped. I changed the indexes and viola it is speedy all round. much appreciated –  l0ft13 Oct 26 '12 at 6:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.