Re: Postgres refusing to use >1 core

On Wed, May 11, 2011 at 9:17 PM, Aren Cambre <aren(at)arencambre(dot)com> wrote:

> *2. Not TxDPS reference markers correspond to TxDOT reference markers.*
>
> Now, if I've matched a route, I have to find the reference marker.
>
> The TxDOT database is pretty good but not 100% complete, so some TxDPS
> tickets' reference markers may not exist in the TxDOT table. Plus, it's
> possible that some TxDPS tickets have the wrong marker.
>
> To compensate, I am looking for the closest reference marker along the
> route that is not more than 50 marker units away, either direction. I've
> again implemented that with multiple queries, where I don't stop until I
> find a match. Suppose I am searching for reference marker 256 on TX 71. The
> queries will be like this:
>
> 1. rte_nm = 'SH71' AND rm = '256' (base marker)
> 2. rte_nm = 'SH71' AND rm = '257' (+1)
> 3. rte_nm = 'SH71' AND rm = '255' (-1)
> 4. rte_nm = 'SH71' AND rm = '258' (+2)
> 5. rte_nm = 'SH71' AND rm = '254' (-2)
> 6. ...
> 7. rte_nm = 'SH71' AND rm = '306' (+50)
> 8. rte_nm = 'SH71' AND rm = '206' (-50)
>
> Assuming a matching route name was found in the prior step, the app will
> have 1 to 101 of these queries for each ticket.
>

This is a perfect example of a place where you could push some work out of
the application and into the database. You can consolidate your 1 to 101
queries into a single query. If you use:

WHERE rte_nm='SH71' AND rm >= 206 AND rm <= 306 ORDER BY abs(rm - 256), rm -
256 DESC LIMIT 1

it will always return the same value as the first matching query from your
list, and will never have to make more than one trip to the database. Your
one trip might be slightly slower than any one of the single trips above,
but it will certainly be much faster in the case where you have to hit any
significant % of your 101 potential queries.

-Eric