Tag Info

New answers tagged

0

YMMV. Views sometimes fail to do the same optimizations, leading to slower execution than performing the SELECT directly. Since your case is straight forward, probably you will incur no noticeable overhead. INSERTs through a VIEW have limitations; sounds like you won't hit them. Read the manual. JOINs is a place where the optimizer may drop the ball; ...


1

Not a complete answer, but it would not fit into a comment. lastval() & currval() What makes you think lastval() is discouraged? Seems like a misunderstanding. In the referenced answer, Craig strongly recommends to use a trigger instead of the rule in a comment. And I agree - except for your special case, obviously. The answer strongly discourages ...


1

Why don't you call a stored procedure via NHibernate instead of asking it to query the view? That would allow you to write your own query and tune as needed.... I don't know NHibernate very well but this blog post looks like a reasonable resource. In case that link dies here are the basic steps: Write your stored procedure to select the data in the most ...


4

So, is there a way to preserve current_user, without giving the dbuser group role direct access to the relations in schema private? You may be able to use a rule, rather than an INSTEAD OF trigger, to provide write access through the view. Views always act with the security rights of the view creator rather than the querying user, but I don't think ...


4

You can create a function like this; CREATE OR REPLACE FUNCTION public.get_messages_by_timestamp ( time_a timestamp, time_b timestamp ) RETURNS TABLE ( recipient varchar, "timestamp" timestamp ) AS $$ BEGIN RETURN QUERY SELECT DISTINCT ON (m.recipient) m.recipient, m."timestamp" FROM messages m LEFT JOIN ...


9

This query: SELECT DISTINCT ON(recipient) * FROM messages LEFT JOIN identities ON messages.recipient = identities.name WHERE messages.timestamp BETWEEN timeA AND timeB ORDER BY recipient, timestamp DESC; says: For all messages between timeA and timeB, find the recipients and for every recipient, find one message (the latest in between timeA and ...


0

Remove the 4 left joins. You are not using their data and they are not needed. It slows everything down, especially if you don't have proper PK and indexes (tell us more) parameter_table.id should be a PK and/or have 1 or 2 indexes on it such as (it really depends on your model): create index idx_parameter_table_id0 on parameter_table(id) where col1 = 0 ...



Top 50 recent answers are included