It might be worthwhile to experiment with 2 new indexes: Create UNIQUE index articles_created_topic_idx on articles(created, topic); Create UNIQUE index articles_topic_created_idx on articles(topic, created);
Probably, one of the two should become your primary key. That will give the optimizer some new choices for plans. > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Jeff Rogers > Sent: Friday, June 18, 2004 12:28 PM > To: [EMAIL PROTECTED] > Subject: [GENERAL] can't win > > > > I have a query that it seems is destined to be slow one way > or another. I > have a table of around 30k articles, categorized by topic and > ordered by date: > > create table articles ( > topic varchar(50), > created date, > data text > ); > > create index articles_topic_idx on articles(topic); > create index articles_created_idx on articles(created); > > If I want to get the 5 most recent articles in a topic, I get > a nice query > plan and a fast query: > # explain select * from articles where topic = 'Example' > order by created desc > limit 5 ; > QUERY PLAN > > > -------------------------------------------------------------- > ----------------- > ----------------------------- > Limit (cost=0.00..646.71 rows=5 width=828) > -> Index Scan Backward using articles_created_idx on articles > (cost=0.00..85202.16 rows=659 width=828) > Filter: (topic = 'Example'::character varying) > (3 rows) > > # select * from articles where topic = 'Example' order by > created desc limit 5 > ; > [.....] > Time: 18.42 ms > > However, if the topic happens to not exist, this query takes > a very long time: # select * from articles where topic = > 'NO-Example' order by created desc > limit 5 ; > [.....] > Time: 1075.36 ms > > If I drop the date index or get more articles (so it doesn't > do the backward > scan on articles_created_idx), then the situation is > reversed: getting the > most recent articles for a topic that exists takes a fair > amount of time, > while getting a topic that does not exist is nearly instantaneous. > > Is there any way I can get the best of both worlds? > > -J > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so > that your > message can get through to the mailing list cleanly > ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
