From: | Sam Saffron <sam(dot)saffron(at)gmail(dot)com> |
---|---|
To: | PGSQL Mailing List <pgsql-general(at)postgresql(dot)org> |
Subject: | Why is posgres picking a suboptimal plan for this query? |
Date: | 2017-05-24 20:42:14 |
Message-ID: | CAAtdryN2iA8aqnmU9zkdmc4pRFPApmh-=b1MGnm1-=4_DvGY2w@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have this query that is not picking the right index unless I hard code dates:
SELECT "topics".* FROM "topics"
WHERE topics.last_unread_at >= '2017-05-11 20:56:24'
"Index Scan using index_topics_on_last_unread_at on topics
(cost=0.41..8.43 rows=1 width=725) (actual time=0.005..0.065 rows=5
loops=1)"
" Index Cond: (last_unread_at >= '2017-05-11 20:56:24'::timestamp
without time zone)"
"Planning time: 0.136 ms"
"Execution time: 0.087 ms"
SELECT "topics".* FROM "topics"
WHERE topics.last_unread_at >= (select first_topic_unread_at from
user_stats us where us.user_id = 1)
"Seq Scan on topics (cost=8.30..24030.12 rows=20555 width=725) (actual
time=3.186..59.636 rows=5 loops=1)"
" Filter: (last_unread_at >= $0)"
" Rows Removed by Filter: 61660"
" InitPlan 1 (returns $0)"
" -> Index Scan using user_stats_pkey on user_stats us
(cost=0.29..8.30 rows=1 width=8) (actual time=0.008..0.009 rows=1
loops=1)"
" Index Cond: (user_id = 1)"
"Planning time: 0.147 ms"
"Execution time: 59.671 ms"
select first_topic_unread_at from user_stats us where us.user_id = 1
"2017-05-11 20:56:24.842356"
The results here simply do not make sense to me, should I be piping
dates in here to avoid this issue and running 2 queries instead of 1?
From | Date | Subject | |
---|---|---|---|
Next Message | Jeff Janes | 2017-05-24 21:43:35 | Re: Why is posgres picking a suboptimal plan for this query? |
Previous Message | Scott Marlowe | 2017-05-24 17:34:19 | Re: Current best practice for maximum shared_buffers settings on big hardware? |