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've got a simple blog database in postgres-8.4 which has two tables, articles and comments. I have a query (generated by Django) that wants to get the latest article of type 'NEWS' and also find the number of comments for that article. It does that with the following query:

SELECT "articles"."id", "articles"."datestamp", "articles"."title", "articles"."shorttitle", "articles"."description", "articles"."markdown", "articles"."body", "articles"."idxfti", "articles"."published", "articles"."type", COUNT("comments"."id") AS "comment__count"
FROM "articles"
LEFT OUTER JOIN "comments" ON ("articles"."id" = "comments"."article_id")
WHERE ("articles"."type"='NEWS')
GROUP BY "articles"."id", "articles"."datestamp", "articles"."title", "articles"."shorttitle", "articles"."description", "articles"."markdown", "articles"."body", "articles"."idxfti", "articles"."published", "articles"."type"
ORDER BY "articles"."datestamp" DESC
LIMIT 1;

None of these tables is particularly large, and yet that query takes 46ms. The execution plan is:

Limit  (cost=119.54..119.58 rows=1 width=1150) (actual time=46.479..46.481 rows=1 loops=1)
   ->  GroupAggregate  (cost=119.54..138.88 rows=455 width=1150) (actual time=46.475..46.475 rows=1 loops=1)
     ->  Sort  (cost=119.54..120.68 rows=455 width=1150) (actual time=46.426..46.428 rows=2 loops=1)
           Sort Key: articles.datestamp, articles.id, articles.title, articles.shorttitle, articles.description, articles.markdown, articles.body, articles.idxfti, articles.published, articles.type
           Sort Method:  quicksort  Memory: 876kB
           ->  Hash Left Join  (cost=11.34..99.45 rows=455 width=1150) (actual time=0.513..2.527 rows=566 loops=1)
                 Hash Cond: (articles.id = comments.article_id)
                 ->  Seq Scan on articles  (cost=0.00..78.84 rows=455 width=1146) (actual time=0.017..0.881 rows=455 loops=1)
                       Filter: ((type)::text = 'NEWS'::text)
                 ->  Hash  (cost=8.93..8.93 rows=193 width=8) (actual time=0.486..0.486 rows=193 loops=1)
                       ->  Seq Scan on comments  (cost=0.00..8.93 rows=193 width=8) (actual time=0.004..0.252 rows=193 loops=1)
 Total runtime: 46.574 ms

The articles table has the following index defined (amongst others):

idx_articles_datestamp" btree (datestamp DESC) CLUSTER

Before I clustered it, the query execution was more in line with the estimates, around 119ms.

To my untrained eye, it looks like the sort is what's taking the most amount of time here. It also seems to be trying to sort on all the GROUP BY fields, the issue being that it's trying to sort on three relatively large fields, body, markdown and idx_fti.

My question is this: Is this an unreasonable amount of time for this query to take, or is there something obvious I've missed that I can use to speed this query up? All the other queries requested by this blog site take around 1-5ms to execute, so this one stands out as taking a long time. I appreciate there's an OUTER JOIN and a SORT, which don't really help. However, I'm not an expert, so if anyone has any suggestions, that'd be hugely useful.

share|improve this question

5 Answers 5

Why is it slow?

I would advise to use the query @ypercube provided in combination with the mentioned indexes. But why is the query you had so slow in comparison?

You did not provide your table definition, but I assume from the column names and what you wrote that you have several (large) character type (text or varchar) columns in the table articles:

title, shorttitle, description, markdown, body, idx_fti

I further assume that you are running your database with a locale other than C. Sorting large text columns according to a locale is rather expensive. The relevant thing is the collation. Check your (current) setting for LC_COLLATE:

SHOW LC_COLLATE;

With PostgreSQL 9.1 or later you can pick a collation for evaluating your expressions ad hoc. With PostgreSQL 8.4, however, this is set at cluster creation time and can't be changed later.

We recently had a related question on SO where after much deliberation and testing we found sorting according to a locale to be the major slowdown.

I expect @ypercube's query to solve that problem radically: No GROUP BY for the long text columns eliminates the expensive sort altogeher. Problem solved.

share|improve this answer

Another way to rewrite the query, with an inline subquery:

SELECT id,
       datestamp,
       title,
       shorttitle,
       description,
       markdown,
       body,
       idxfti,
       published,
       type,
       ( SELECT COUNT(*) 
         FROM comments 
         WHERE articles.id = comments.article_id
       ) AS comment__count
FROM articles 
WHERE type = 'NEWS'
ORDER BY datestamp DESC 
LIMIT 1
share|improve this answer
    
+1 This should be the best solution. In connection with an index on comments(article) and the above mentioned index on articles(type, datestamp DESC). The query uses LIMIT 1, so only relevant rows should be fetched from article and counted. –  Erwin Brandstetter Apr 30 '12 at 14:04

If articles.type is less than about 10% of the table, you might benefit from an index on that column. You can almost certainly benefit from an index on comments.article_id, if you don't already have one.

In addition to that, if you haven't adjusted the cost factors in your configuration, you could try lowering random_page_cost to somewhere in the 1.0 to 2.0 range; if your active data set is fully cached, you should probably take that and seq_page_cost down to 0.1. You should probably increase cpu_tuple_cost to somewhere in the range of 0.03 to 0.05. effective_cache_size should be the sum of shared_buffers and whatever your OS shows as cache space.

share|improve this answer
    
I did already have an index on comments.article_id, and adding one to articles.type didn't seem to do a lot. I'll try tweaking some of the server settings though, thanks for that. –  growse Apr 29 '12 at 18:27

You might want to try to remove the group by and use a windowing function for counting. That removes the need to group by/sort on all columns:

SELECT articles.id,
       articles.datestamp,
       articles.title,
       articles.shorttitle,
       articles.description,
       articles.markdown,
       articles.body,
       articles.idxfti,
       articles.published,
       articles.type,
       COUNT(comments.id) over () AS comment__count
FROM articles 
  LEFT OUTER JOIN comments ON (articles.id = comments.article_id)
WHERE (articles.type = 'NEWS')
ORDER BY articles.datestamp DESC 
LIMIT 1
share|improve this answer
    
An index on (type ASC, datestamp DESC) would additionally improve performance, wouldn't it? –  ypercube Apr 29 '12 at 16:23
    
@ypercube: yes, might very well help as well to speed up the where condition. –  a_horse_with_no_name Apr 29 '12 at 16:28
    
Yeah, if the query can be rewritten, there are a number of good alternatives, of which this might well be the best. Even more options exist on version 9.1, where if you GROUP BY the primary key column(s) you don't need to include any of the other columns from that table. –  kgrittn Apr 29 '12 at 17:02
    
I tried adding an index with create index idx_articles_type_datestamp on articles(type ASC, datestamp DESC) but that didn't seem to make much difference. I'll see what I can do with forcing django to do a COUNT OVER, but am I right in thinking that's Pg-specific? –  growse Apr 29 '12 at 18:22
3  
@growse: windowing functions are standard SQL and supported by a lot of modern DBMS (Oracle, PostgreSQL, DB2, SQL Server, Teradata, Firebird 3.0) –  a_horse_with_no_name Apr 29 '12 at 19:00

Maybe you could do some tests modifying the value of work_mem. There you can find instructions in order to find how much memory is used in the sorting operations.

share|improve this answer
    
When a sort does not fit into work_mem, it spills out to the disk. If happens, this is shown in the query plan. On the other hand, the sorting here took 876 kB of memory. It is very unlikely that this is bigger than the work_mem of any relatively recent PG instances. –  dezso Jan 15 '13 at 13:27

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.