All Questions

Filter by
Sorted by
Tagged with
2
votes
2answers
33 views

Postgresql expanding view multiple times instead of executing it once

I have a rahter long subquery which I store as a view CREATE VIEW scans_minmax AS SELECT tmp.source, value ->> 'Ok' AS "Ok", ...
2
votes
0answers
27 views

Is it possible to replace count distinct inside Correlated sub-queries?

I'm trying to optimize the query bellow. The execution Plan is showing multiple sorting before aggregations (I think this is due to the count distincts in the correlated sub-queries)... Is it ...
0
votes
0answers
55 views

PostgreSQL - Optimize query with multiple subqueries

I have 2 tables, users and sessions. The tables look like this: users - id (int), name (varchar) sessions - id (int), user_id (int), ip (inet), cookie_identifier (varchar) All columns have an index....
0
votes
1answer
62 views

How to make subtraction without repeating the subqueries? [closed]

I need to efficiently obtain: players who play more than one world cup and who have never been champion. primary-key | foreign-key | pk-and-fk PLAYER<player_id, player_name, country_player, .....
2
votes
1answer
375 views

Best way to move CTE into query so planner can better optimize and I can add indexes speed it up? [closed]

I'm trying to incorporate the CTE into my final query. I can do it, but it slows it down a lot because I have to do the same sub query for CTE d over and over again (I think): with d AS( SELECT ...
0
votes
1answer
218 views

Can't optimize a nested query using indexes

There are two tables containing hundreds of thousands of records: client and task. It's necessary to query the following: SELECT id, (SELECT status FROM task t WHERE t.client_id = c.id AND ...
2
votes
2answers
1k views

Postgres optimizing nested exists

I have a table of events that each take place during a time range, and I'm trying to create a template for a query which can find events that take place within a specified time period of correlated ...
3
votes
2answers
7k views

conditional subquery

I have the following query: SELECT id, email, first_name as "firstName", last_name as "lastName", is_active as "isActive", password, access, CASE WHEN access < 3 THEN ( ...
0
votes
1answer
128 views

A multi-table JOIN query as subquery to a query that JOINS the same tables. Good or bad?

I have the following tables in my postgres database - power_relations - (id, properties) - power_relation_members - (id, power_relation_id, member_id, member_type) - power_lines - (...
1
vote
1answer
3k views

Reuse subquery as in another result

In a typical social-network-like application I have the following query that gathers news from a set of followed channels. The main query gets the news, while the subquery gets the followed channels: ...
1
vote
2answers
533 views

optimization of expensive subquery

The following query gets the last report (where latitude and longitude and secs are <> 0) associated with the specified units: SELECT reports.* FROM reports INNER JOIN ...