SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

Symbolic Butt: Mar 22, 2009; (_!_); Buglord

I have a stored procedure butt() that takes an _id and returns an integer, I have a query that uses it to get the top 10 buttest ids. Like this:

code:

select _id from
(select _id, butt(_id) from my_table) t
where butt is not null
order by butt desc
limit 10

butt is a somewhat complicated (and expensive) procedure that uses data from another table. But there's a field in my_table that gives a hint of butt's value, like butt will never be bigger than this value. Is there a way to express this constraint in the query to speed things up? By the way I'm on postgresql.

# ¿ Feb 7, 2017 19:24

Adbot: ADBOT LOVES YOU

# ¿ May 21, 2024 07:30

Symbolic Butt: Mar 22, 2009; (_!_); Buglord

Vargatron posted:

Wouldn't adding additional conditions or operators slow down performance worse than having a less restrictive filter?

Not really in this case because butt is way more expensive than filtering.

I just thought of a way to do it:

code:

select _id, butt(_id)
from my_table
where butt(_id) is not null
and max_butt >= butt(_id)
order by butt(_id) desc
limit 10

it didn't help as much as I expected but oh well... At least I feel like this is the kind of information that I wanted to make it explicit to the database.

Symbolic Butt fucked around with this message at 23:00 on Feb 7, 2017

# ¿ Feb 7, 2017 21:05

Symbolic Butt: Mar 22, 2009; (_!_); Buglord

The way I understand it is that LIKE is part of the SQL standard which implements a less expressive subset of regex.

~ is a postgres thing where they implemented full posix extended regex.

I heard LIKE is supposed to be more efficient than ~ but I I'm not sure if this is relevant in most cases.

# ¿ Apr 23, 2017 04:06

Symbolic Butt: Mar 22, 2009; (_!_); Buglord

loving incredible. Thanks for posting this Nth Doctor!

# ¿ Jun 6, 2017 15:05

Symbolic Butt: Mar 22, 2009; (_!_); Buglord

I'm seconding SQL Antipatterns, it surprisingly became one of my favorite programming books ever, I keep going back to it.

# ¿ Mar 3, 2024 23:50

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'