can't win

Поиск
Список
Период
Сортировка
От Jeff Rogers
Тема can't win
Дата
Msg-id 200406181927.i5IJRnP28361@findlaw.com.
обсуждение исходный текст
Ответы Re: can't win  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список pgsql-general
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





В списке pgsql-general по дате отправления:

Предыдущее
От: ecomeau@signiant.com (Eric)
Дата:
Сообщение: Re: PgSQL shut down
Следующее
От: "Dann Corbit"
Дата:
Сообщение: Re: can't win