5 ms·
Yep, a few. 1. Temporary views. This one's my favourite but not everything supports it and sometimes DBMSs have weird limitations, not like allowing parameter
by ketralnis 10y ago
Yep, a few.
1. Temporary views. This one's my favourite but not everything supports it and sometimes DBMSs have weird limitations, not like allowing parameter binding in them.
2. Temporary tables, as you mention
3. CTEs ("with expressions"). These would be my favourite if they weren't an optimisation fence in postgres.
WITH active_users
AS (select * from users where user.login > 0)
select * from active_users where active_users.country is not null;
4. If you're only using it once, a subselect lets you add a name to it if that's all you're after
select *
from (select * from users where user.login > 0) AS active_users
where active_users.country is not null;
5. If you're using active_users frequently and want it to be globally accessible, a regular view. Often you can make a DB much more useable with a few well placed views like this and you'll need a few less abstractions as well.
- weaksauce 10y agoThanks for the options. can you expand on why CTEs are a postgres optimization fence? as for 4: I am aware of the named subselect but once you add a few of those to the main select statement you start to get into unreadable territory. back to CTEs, it seems like it should be an easy substitution problem for the postgres query planner. I am not a DB developer so I am probably not aware of all the issues. The CTE seems like the thing I was trying to describe though. Do you think that the optimizations would be more possible with begin and end scopes? It seems like they are part of the same select statement so i doubt scopes would help it.
- gleb 10y agoIn practice optimization fence is not normally a problem, and can even be an advantage. The general rule is - just use CTEs. And the specific rule is - learn to profile and optimize using EXPLAIN ANALYZE