On Friday, March 25, 2016 at 10:57:00 AM UTC-7, Kaito Michishige wrote:
>
>
>
> For many years Sequel has supported this:
>>
>> where(Sequel.extract(:month, :date)=>d.month, Sequel.extract(:year,
>> :date)=>d.year)
>>
>
> This seems to generate the same SQL as what I posted above.
>
> Just for convenience, here's the generated SQL:
>
> WHERE ((extract(month FROM "date") = 3) AND (extract(year FROM "date") =
> 2016))"
>
>
>> However, in your example you are probably better off doing:
>>
>> start = Date.new(d.year, d.month)
>> finish = start >> 1
>> where(:date=>start...finish)
>>
>> as that is more likely to use an index.
>>
>
> As far as I can tell, this would work as well. Here's the generated SQL:
>
> WHERE (("date" >= '2016-03-01') AND ("date" < '2016-04-01'))"
>
> Why does the first option not use an index, assuming one is present on the
> column?
>
The query planner would need to have knowledge of extract for that to work,
and I'm guessing it doesn't. Note that you can get the first type of query
to use an index on PostgreSQL, but you'll have to create an index for it:
add_index [Sequel.extract(:year, :date), Sequel.extract(:month, :date)]
There's really no advantage to doing so, though, if you can just use the
second query.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/sequel-talk.
For more options, visit https://groups.google.com/d/optout.
