For a website that displays recent news, I have this query on the home page:

FROM "cms_news"
  NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00') AND 
       "cms_news"."state" = 'published'
ORDER BY "cms_news"."flash" DESC, "cms_news"."timestamp" DESC LIMIT 7;

Here's the table definition:

CREATE TABLE cms_news (
    id integer DEFAULT nextval('cms_news_id_seq'::regclass) NOT NULL,
    created timestamp with time zone NOT NULL,
    modified timestamp with time zone NOT NULL,
    title character varying(510) NOT NULL,
    text text NOT NULL,
    "timestamp" timestamp with time zone NOT NULL,
    state character varying(20) NOT NULL,
    flash boolean NOT NULL,

With about 67000 entries in the table, the above query to select the newest 7 published news entries takes about 100ms, which seems a bit much. Here's the output of EXPLAIN:

EXPLAIN SELECT * FROM "cms_news" WHERE (NOT ("cms_news"."timestamp" >= '2013-08-08 13:32:39.778988+00:00' ) AND "cms_news"."state" = 'published' ) ORDER BY "cms_news"."flash" DESC, "cms_news"."timestamp" DESC LIMIT 7;
                                                                QUERY PLAN                                                                 
 Limit  (cost=13600.34..13600.36 rows=7 width=1274)
   ->  Sort  (cost=13600.34..13768.27 rows=67171 width=1274)
         Sort Key: flash, "timestamp"
         ->  Seq Scan on cms_news  (cost=0.00..12321.62 rows=67171 width=1274)
               Filter: (("timestamp" < '2013-08-08 15:32:39.778988+02'::timestamp with time zone) AND ((state)::text = 'published'::text))

I'm a complete amateur (as you might have guessed already), but it looks like sorting is the real problem. What kind of index would help me here?

  • Why do you have the 'NOT' operator on the timestamp WHERE clause? That would seem to imply you want records older than the given date.
    – Hannah Vernon
    Commented Aug 8, 2013 at 14:14
  • In SQL Server, you could add an index on 'timestamp', filtered by the boolean field.
    – Hannah Vernon
    Commented Aug 8, 2013 at 14:16
  • @MaxVernon: I'm excluding all news entries with a timestamp higher than NOW(). The system allows entering news entries with a future publishing date, those are filtered out. Commented Aug 8, 2013 at 14:19
  • 1
    Why not put NOW() in your question code to eliminate confusion...
    – Hannah Vernon
    Commented Aug 8, 2013 at 15:11
  • @MaxVernon Same in PostgreSQL with a partial index, but it won't help since the boolean is part of the sort clause. Commented Aug 8, 2013 at 16:04

1 Answer 1


Try creating the following index, running ANALYZE cms_news, and posting the EXPLAIN ANALYZE plan again

CREATE INDEX cms_news_flash_ts_idx ON cms_news ( flash desc, timestamp desc );

Tip: You can post your EXPLAIN ANALYZE plans to http://explain.depesz.com/ and post the link back here. I've found it to be a pretty helpful tool over the years.

  • Oh, wow, that reduced the time for this query from 100ms to about 1ms. Awesome, thanks! Commented Aug 8, 2013 at 15:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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