Обсуждение: How do I bump a row to the front of sort efficiently
I have this query: select * from topics order by case when id=1 then 0 else 1 end, bumped_at desc limit 30 It works fine, bumps id 1 to the front of the sort fine but is terribly inefficient and scans OTH "select * from topics where id = 1" is super fast "select * from topics order by bumped_at desc limit 30" is super fast Even this is fast, and logically equiv as id is primary key unique select * from topic where id = 1000 union all select * from ( select * from topics where id <> 1000 order by bumped_at desc limit 30 ) as x limit 30 However, the contortions on the above query make it very un-ORM friendly as I would need to define a view for it but would have no clean way to pass limits and offsets in. Is there any clean technique to bump up particular rows to the front of a sort if a certain condition is met without paying a huge performance hit?
sam.saffron wrote > I have this query: > > select * from topics > order by case when id=1 then 0 else 1 end, bumped_at desc > limit 30 > > It works fine, bumps id 1 to the front of the sort fine but is > terribly inefficient and scans > > OTH > > "select * from topics where id = 1" is super fast > > "select * from topics order by bumped_at desc limit 30" is super fast > > Even this is fast, and logically equiv as id is primary key unique > > select * from topic > where id = 1000 > union all > select * from ( > select * from topics > where id <> 1000 > order by bumped_at desc > limit 30 > ) as x > limit 30 > > > However, the contortions on the above query make it very un-ORM > friendly as I would need to define a view for it but would have no > clean way to pass limits and offsets in. > > Is there any clean technique to bump up particular rows to the front > of a sort if a certain condition is met without paying a huge > performance hit? CREATE FUNCTION ...? Probably with a VARIADIC argument. David J. -- View this message in context: http://postgresql.nabble.com/How-do-I-bump-a-row-to-the-front-of-sort-efficiently-tp5836354p5836356.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Mon, Feb 2, 2015 at 1:16 AM, Sam Saffron <sam.saffron@gmail.com> wrote:
However, the contortions on the above query make it very un-ORM
friendly as I would need to define a view for it but would have no
clean way to pass limits and offsets in.
This is why ORMs are bad. They make hard problems much harder, and the only benefit is that they maybe make easy problems a little quicker. The cost/savings is heavily skewed toward the cost, since there's no upper bound on the cost and there is a pretty small lower bound on the savings. Micro-ORMs tend to do a better job of not shielding you from (or rather, getting in the way of) the SQL while still providing some good result-to-object translation. Whether even that is necessary depends on your language, though. (For example, in Python, psycopg2 has a built in way of spitting out namedtuples, which means you get result-to-object translation out of the box. That makes even a micro-ORM pretty unnecessary. On the other hand, a micro-ORM that does this well without blocking you from the SQL, such as PetaPOCO, is a boon in .NET.)
If you can, your best bet would probably be to find a way to get your ORM to execute raw SQL (with good parametrization to prevent injection attacks!!!!) and be done with it. It took me way too much experience fighting with an ORM on complicated queries to realize that.
On Tue, Feb 3, 2015 at 9:33 PM, BladeOfLight16 <bladeoflight16@gmail.com> wrote:
This is why ORMs are bad. They make hard problems much harder, and the only benefit is that they maybe make easy problems a little quicker. The cost/savings is heavily skewed toward the cost, since there's no upper bound on the cost and there is a pretty small lower bound on the savings. Micro-ORMs tend to do a better job of not shielding you from (or rather, getting in the way of) the SQL while still providing some good result-to-object translation. Whether even that is necessary depends on your language, though. (For example, in Python, psycopg2 has a built in way of spitting out namedtuples, which means you get result-to-object translation out of the box. That makes even a micro-ORM pretty unnecessary. On the other hand, a micro-ORM that does this well without blocking you from the SQL, such as PetaPOCO, is a boon in .NET.)If you can, your best bet would probably be to find a way to get your ORM to execute raw SQL (with good parametrization to prevent injection attacks!!!!) and be done with it. It took me way too much experience fighting with an ORM on complicated queries to realize that.
Er, *pretty small upper bound on the savings.