All Questions
23 questions
2
votes
4
answers
2k
views
Why is my big Postgres query faster if I separate it into 3 smaller queries?
I have a big/complex query (with an OR clause and 2 subqueries that have multiple joins and an IN condition). Executing the big query is slow.
However, if I break my big query down into 3 smaller ...
- 121
0
votes
2
answers
3k
views
Query with CASE in subquery join clause is very slow on PostgreSQL but fast on Oracle and MariaDB
We have an attribute-oriented data management system with bitemporality and flexible tenant priorization logic. Deletion of records is done by inserting the same row in DELETED status. Tenants may ...
- 11
1
vote
1
answer
404
views
basic subquery evaluation
I am new at subqueries. Is there a general strategy on how basic subqueries are evaluated and executed (besides from sql engines automatically optimizing them). For example, consider this select query:...
1
vote
2
answers
128
views
Index isn't used when value is a result of a subquery or from a wrapper query
I have a simple one-to-many relationship: accounts and events where an account may have many events.
accounts
id: uuid
...more irrelevant fields
events
id: serial
type: enum ['activated', '...
- 111
0
votes
1
answer
383
views
SQL (Postgres): Subquery in WHERE clause with IN operator is slow
I have the following SQL Statement:
select count(*)
from person p
cross join task t
where t.id in (
select tp.task_id
from task_participant tp
where tp.person_id = p.id )
and p.id = 1234
...
- 111
2
votes
2
answers
1k
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",
...
- 123
1
vote
3
answers
4k
views
How to apply outer limit offset and filters in the subquery to avoid grouping over the complete table used in subquery in Postgresql
I have legacy tables similar to the following:
employee
------------------------------------
| employee_id | name
------------------------------------
| 1 | David
| 2 ...
- 111
1
vote
1
answer
516
views
Update "oldest" row
I need to update the "oldest" row in the database (for now, its limit 1, but I need to also be able to set it to n).
I'm essentially doing a constant stream of "update the oldest rows and retrieve ...
- 223
3
votes
0
answers
2k
views
Why is Postgres choosing a Hash-Join instead of a Nested Join that is 200x faster?
I run the following query against Postgres 9.5 which returns article information along with the currently valid price:
WITH selected_article_rm(id) AS (
VALUES
('80512e52-a406-4a9b-a8df-...
- 256
0
votes
1
answer
236
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, .....
- 109
1
vote
2
answers
1k
views
How to use GROUP BY in an efficient way without losing attributes? [closed]
I am working with PostgreSQL.
My idea is to group players who appear more than 3 times in table-a.
table-a = <player_id, year_world, c, d, e>
With the first subquery I get player_id and the ...
- 109
5
votes
2
answers
238
views
A better way to write this query?
I have the following schema for my database (Postgresql 10):
CREATE TABLE "PulledTexts" (
"Id" serial PRIMARY KEY,
"BaseText" TEXT,
"CleanText" TEXT
);
CREATE TABLE "UniqueWords" (
"Id" serial ...
- 153
2
votes
1
answer
1k
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
...
3
votes
3
answers
5k
views
How to avoid a subquery in FILTER clause?
Schema:
CREATE TABLE "applications" (
"id" SERIAL NOT NULL PRIMARY KEY,
"country" VARCHAR(2) NOT NULL,
"created" TIMESTAMP WITH TIME ...
- 475
8
votes
3
answers
13k
views
Performance of count(*) in subquery
Suppose we have the following queries:
1.
SELECT COUNT(*) FROM some_big_table WHERE some_col = 'some_val'
2.
SELECT COUNT(*) FROM ( SELECT * FROM some_big_table WHERE some_col = 'some_val' ...
- 195
2
votes
2
answers
3k
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 ...
- 185
0
votes
1
answer
251
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 - (...
7
votes
2
answers
11k
views
best way to avoid redundant aggregate functions and/or group by columns
Suppose I have two tables:
Foo:
id
baz
Bar:
id
foo_id
boom
So a Foo has many Bars. I frequently find myself in situations where I need to compute an aggregate across the Bars for a given set of ...
- 183
1
vote
0
answers
227
views
Moving slow sub-query to With clause
I am trying to optimize this query :
SELECT sum(a-alias.s_count) AS count
FROM a-table a-alias
WHERE
(
a-alias.created_at_minute_id >=
(floor(extract(epoch from(
select ...
- 111
15
votes
3
answers
24k
views
PostgreSQL query very slow when subquery added
I have a relatively simple query on a table with 1.5M rows:
SELECT mtid FROM publication
WHERE mtid IN (9762715) OR last_modifier=21321
LIMIT 5000;
EXPLAIN ANALYZE output:
Limit (cost=8.84..12.86 ...
- 901
4
votes
1
answer
471
views
Combine multiple queries into 1 query
I have a message table in Postgres 9.4 that contains a words field of array type, with random words of a message. Currently I have millions of messages:
\d messages
Table "...
- 343
1
vote
2
answers
621
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
...
- 1,889
4
votes
1
answer
9k
views
PostgreSQL 9.1 - Query on VIEWS taking a lot of time
Using PostgreSQL 9.1, we are having problems while executing queries on a VIEW of PostgreSQL. Following is the situation:
We have a partitioned Table "buz_scdr" over which we have built a VIEW "...
- 143