Some simple searches including metatags result in timeouts

[ToksT]

"gentags:1 -tagme": http://danbooru.donmai.us/posts?tags=gentags:1+-tagme
"date:2006-01-01..2006-05-01": http://danbooru.donmai.us/posts?tags=date:2006-01-01..2006-05-01
Both result in timeouts, even though they shouldn't even be that large.

For date searches, some work and some don't. This works, even though it should be larger than the one above:
"date:2006-01-01..2012-01-01": http://danbooru.donmai.us/posts?tags=date:2006-01-01..2012-01-01

[ToksT]

I'm still not entirely sure what the cause is, but I noticed something for those date searches: If you changed the ordering to posts.created_at DESC instead of the normal posts.id DESC, then they'll be much faster.

Maybe changing the default order to created at if and only if the date: metatag is present would be an idea.

[evazion]

gentags:1 -tagme is slow because tag_count_general isn't indexed, -tagme can't be indexed, so the query degrades to a full table scan. tag_count_general isn't worth indexing IMO, so it can't be helped.

date:2006-01-01..2006-05-01 is slow because instead of filtering by the date, it does a downwards index scan on posts.id, which leads to scanning ~2.5 million posts until it reaches 2006:

EXPLAIN (analyze) SELECT "posts".* FROM "posts" WHERE (true) AND (posts.created_at BETWEEN '2006-01-01 05:00:00.000000' AND '2006-05-01 04:00:00.000000') AND (true /* PostSets::Post#posts:2 */) ORDER BY posts.id DESC LIMIT 20 OFFSET 0;

 Limit  (cost=0.43..1055.76 rows=20 width=1171) (actual time=2158.938..2158.962 rows=20 loops=1)
   ->  Index Scan Backward using posts_pkey on posts  (cost=0.43..881832.15 rows=16712 width=1171) (actual time=2158.935..2158.958 rows=20 loops=1)
         Filter: ((created_at >= '2006-01-01 05:00:00'::timestamp without time zone) AND (created_at <= '2006-05-01 04:00:00'::timestamp without time zone))
         Rows Removed by Filter: 2540515
 Planning time: 0.367 ms
 Execution time: 2159.024 ms

Ordering by posts.created_at DESC allows it to use the created_at index to both filter and order by date:

EXPLAIN (analyze) SELECT "posts".* FROM "posts" WHERE (true) AND (posts.created_at BETWEEN '2006-01-01 05:00:00.000000' AND '2006-05-01 04:00:00.000000') AND (true /* PostSets::Post#posts:2 */) ORDER BY posts.created_at DESC LIMIT 20 OFFSET 0;

 Limit  (cost=0.43..28.07 rows=20 width=1171) (actual time=0.146..0.166 rows=20 loops=1)
   ->  Index Scan Backward using index_posts_on_created_at on posts  (cost=0.43..23096.43 rows=16712 width=1171) (actual time=0.145..0.162 rows=20 loops=1)
         Index Cond: ((created_at >= '2006-01-01 05:00:00'::timestamp without time zone) AND (created_at <= '2006-05-01 04:00:00'::timestamp without time zone))
 Planning time: 0.371 ms
 Execution time: 0.253 ms