current community

your communities

more stack exchange communities

Stack Exchange
tour help
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.
up vote 1 down vote favorite

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

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;

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?

share|improve this question
    
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. –  Max Vernon Aug 8 '13 at 14:14
    
In SQL Server, you could add an index on 'timestamp', filtered by the boolean field. –  Max Vernon Aug 8 '13 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. –  Benjamin Wohlwend Aug 8 '13 at 14:19
    
Why not put NOW() in your question code to eliminate confusion... –  Max Vernon Aug 8 '13 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. –  Craig Ringer Aug 8 '13 at 16:04

1 Answer 1

up vote 1 down vote accepted

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.

share|improve this answer
    
Oh, wow, that reduced the time for this query from 100ms to about 1ms. Awesome, thanks! –  Benjamin Wohlwend Aug 8 '13 at 15:33

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.