Postgres / slow query / order byPostgreSQL 9.2 (PostGIS) performance problemHow can I speed up a Postgres...
Can "ee" appear in Latin?
Meaning of 折らで in a haiku
Identical projects by students at two different colleges: still plagiarism?
Why do BLDC motor (1kW) controllers have so many mosfets?
Does the kobold player race feature, Pack Tactics, give ranged attacks advantage?
How can I portray body horror and still be sensitive to people with disabilities?
Is layered encryption more secure than long passwords?
Can I reorder the coordinates of a line when importing into QGIS a WKT linestring?
Have any astronauts or cosmonauts died in space?
Why is opening a file faster than reading variable content?
Why don't reads from /dev/zero count as I/O?
Is it ethical to apply for a job on someone's behalf?
boss asked me to sign a resignation paper without a date on it along with my new contract
How do I write a maintainable, fast, compile-time bit-mask in C++?
Coworker is trying to get me to sign his petition to run for office. How to decline politely?
Are encryption algorithms with fixed-point free permutations inherently flawed?
Do error bars on probabilities have any meaning?
How to encourage team to refactor
How does Artisan's Blessing handle rusted and mistreated weapons?
Is it possible to detect 100% of SQLi with a simple regex?
Why did some CPUs use two Read/Write lines, and others just one?
Why didn't Lorentz conclude that no object can go faster than light?
What are Holorydmachines?
How to write painful torture scenes without being over-the-top
Postgres / slow query / order by
PostgreSQL 9.2 (PostGIS) performance problemHow can I speed up a Postgres query containing lots of Joins with an ILIKE conditionpostgres explain plan with giant gaps between operationsHow to optimize multiple ORDER BYs?Slow fulltext search due to wildly inaccurate row estimatespostgresql 9.2 hash join issueSorting killing my postgresql queryWhy is this query with WHERE, ORDER BY and LIMIT so slow?How to index two tables for JOINed query optimisationPerformance difference in accessing differrent columns in a Postgres Table
I'm joining two tables, filter out results by second table and order results by first table's ID.
The query is super-slow on Postgres Database. I see issue is in ORDER BY statement. But increasing work_mem (SET LOCAL work_mem = '256MB';) didn't help.
select A.a_id
from B
inner join A
on A.b_id=B.b_id
where
A.date_one between '2012-01-07 04:05:06' and '2019-01-08 03:07:06'
or A.date_two between '2012-01-07 04:05:06' and '2019-01-08 03:05:06'
order by A.a_id ASC
limit 100;
Execution plan:
"Limit (cost=690932.94..690933.19 rows=100 width=8) (actual time=19412.278..19412.344 rows=100 loops=1)"
" Buffers: shared hit=12521 read=287185"
" -> Sort (cost=690932.94..698624.09 rows=3076458 width=8) (actual time=19412.274..19412.293 rows=100 loops=1)"
" Sort Key: A.a_id"
" Sort Method: top-N heapsort Memory: 29kB"
" Buffers: shared hit=12521 read=287185"
" -> Hash Join (cost=34731.92..573352.93 rows=3076458 width=8) (actual time=822.673..19052.607 rows=417486 loops=1)"
" Hash Cond: (A.b_id = BB.b_id)"
" Buffers: shared hit=12521 read=287185"
" -> Seq Scan on A (cost=0.00..432736.38 rows=7512005 width=16) (actual time=0.044..9306.081 rows=7512001 loops=1)"
" Filter: (((date_one >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_one <= '2019-01-08 03:07:06-05'::timestamp with time zone)) OR ((date_two >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_two <= '2019-01-08 03:05:06-05'::timestamp with time zone)))"
" Rows Removed by Filter: 18"
" Buffers: shared hit=12198 read=270298"
" -> Hash (cost=24997.52..24997.52 rows=778752 width=8) (actual time=820.550..820.550 rows=377405 loops=1)"
" Buckets: 131072 Batches: 1 Memory Usage: 14743kB"
" Buffers: shared hit=323 read=16887"
" -> Seq Scan on fs_invoice_quote invoicequo0_ (cost=0.00..24997.52 rows=778752 width=8) (actual time=0.007..471.024 rows=777884 loops=1)"
" Buffers: shared hit=323 read=16887"
"Total runtime: 19412.432 ms"
Please suggest.
postgresql join order-by
edited Jan 27 at 11:35
McNets
15.9k42061
asked Jan 26 at 23:54
Maksim KaratkouMaksim Karatkou
112
|
show 5 more comments
I'm joining two tables, filter out results by second table and order results by first table's ID.
The query is super-slow on Postgres Database. I see issue is in ORDER BY statement. But increasing work_mem (SET LOCAL work_mem = '256MB';) didn't help.
select A.a_id
from B
inner join A
on A.b_id=B.b_id
where
A.date_one between '2012-01-07 04:05:06' and '2019-01-08 03:07:06'
or A.date_two between '2012-01-07 04:05:06' and '2019-01-08 03:05:06'
order by A.a_id ASC
limit 100;
Execution plan:
"Limit (cost=690932.94..690933.19 rows=100 width=8) (actual time=19412.278..19412.344 rows=100 loops=1)"
" Buffers: shared hit=12521 read=287185"
" -> Sort (cost=690932.94..698624.09 rows=3076458 width=8) (actual time=19412.274..19412.293 rows=100 loops=1)"
" Sort Key: A.a_id"
" Sort Method: top-N heapsort Memory: 29kB"
" Buffers: shared hit=12521 read=287185"
" -> Hash Join (cost=34731.92..573352.93 rows=3076458 width=8) (actual time=822.673..19052.607 rows=417486 loops=1)"
" Hash Cond: (A.b_id = BB.b_id)"
" Buffers: shared hit=12521 read=287185"
" -> Seq Scan on A (cost=0.00..432736.38 rows=7512005 width=16) (actual time=0.044..9306.081 rows=7512001 loops=1)"
" Filter: (((date_one >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_one <= '2019-01-08 03:07:06-05'::timestamp with time zone)) OR ((date_two >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_two <= '2019-01-08 03:05:06-05'::timestamp with time zone)))"
" Rows Removed by Filter: 18"
" Buffers: shared hit=12198 read=270298"
" -> Hash (cost=24997.52..24997.52 rows=778752 width=8) (actual time=820.550..820.550 rows=377405 loops=1)"
" Buckets: 131072 Batches: 1 Memory Usage: 14743kB"
" Buffers: shared hit=323 read=16887"
" -> Seq Scan on fs_invoice_quote invoicequo0_ (cost=0.00..24997.52 rows=778752 width=8) (actual time=0.007..471.024 rows=777884 loops=1)"
" Buffers: shared hit=323 read=16887"
"Total runtime: 19412.432 ms"
Please suggest.
postgresql join order-by
edited Jan 27 at 11:35
McNets
15.9k42061
asked Jan 26 at 23:54
Maksim KaratkouMaksim Karatkou
112
Have you tried applying indexes to the date columns, and rewriting this as aUNIONquery?
– Randolph West
Jan 27 at 0:29
Isdate_twoin table A or B?
– ypercubeᵀᴹ
Jan 27 at 0:55
Hi @Randolph, could you please provide more details re. UNION approach and how that could be helpful to tackle order by issue?
– Maksim Karatkou
Jan 27 at 1:47
the date_two is in table A (updated the question)
– Maksim Karatkou
Jan 27 at 1:48
1
Can you add theCREATE TABLEstatements for both tables? Is there a foreign key fromA (b_id) REFERENCES B (b_id)?
– ypercubeᵀᴹ
Jan 27 at 12:29
|
show 5 more comments
I'm joining two tables, filter out results by second table and order results by first table's ID.
The query is super-slow on Postgres Database. I see issue is in ORDER BY statement. But increasing work_mem (SET LOCAL work_mem = '256MB';) didn't help.
select A.a_id
from B
inner join A
on A.b_id=B.b_id
where
A.date_one between '2012-01-07 04:05:06' and '2019-01-08 03:07:06'
or A.date_two between '2012-01-07 04:05:06' and '2019-01-08 03:05:06'
order by A.a_id ASC
limit 100;
Execution plan:
"Limit (cost=690932.94..690933.19 rows=100 width=8) (actual time=19412.278..19412.344 rows=100 loops=1)"
" Buffers: shared hit=12521 read=287185"
" -> Sort (cost=690932.94..698624.09 rows=3076458 width=8) (actual time=19412.274..19412.293 rows=100 loops=1)"
" Sort Key: A.a_id"
" Sort Method: top-N heapsort Memory: 29kB"
" Buffers: shared hit=12521 read=287185"
" -> Hash Join (cost=34731.92..573352.93 rows=3076458 width=8) (actual time=822.673..19052.607 rows=417486 loops=1)"
" Hash Cond: (A.b_id = BB.b_id)"
" Buffers: shared hit=12521 read=287185"
" -> Seq Scan on A (cost=0.00..432736.38 rows=7512005 width=16) (actual time=0.044..9306.081 rows=7512001 loops=1)"
" Filter: (((date_one >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_one <= '2019-01-08 03:07:06-05'::timestamp with time zone)) OR ((date_two >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_two <= '2019-01-08 03:05:06-05'::timestamp with time zone)))"
" Rows Removed by Filter: 18"
" Buffers: shared hit=12198 read=270298"
" -> Hash (cost=24997.52..24997.52 rows=778752 width=8) (actual time=820.550..820.550 rows=377405 loops=1)"
" Buckets: 131072 Batches: 1 Memory Usage: 14743kB"
" Buffers: shared hit=323 read=16887"
" -> Seq Scan on fs_invoice_quote invoicequo0_ (cost=0.00..24997.52 rows=778752 width=8) (actual time=0.007..471.024 rows=777884 loops=1)"
" Buffers: shared hit=323 read=16887"
"Total runtime: 19412.432 ms"
Please suggest.
postgresql join order-by
edited Jan 27 at 11:35
McNets
15.9k42061
asked Jan 26 at 23:54
Maksim KaratkouMaksim Karatkou
112
I'm joining two tables, filter out results by second table and order results by first table's ID.
The query is super-slow on Postgres Database. I see issue is in ORDER BY statement. But increasing work_mem (SET LOCAL work_mem = '256MB';) didn't help.
select A.a_id
from B
inner join A
on A.b_id=B.b_id
where
A.date_one between '2012-01-07 04:05:06' and '2019-01-08 03:07:06'
or A.date_two between '2012-01-07 04:05:06' and '2019-01-08 03:05:06'
order by A.a_id ASC
limit 100;
Execution plan:
"Limit (cost=690932.94..690933.19 rows=100 width=8) (actual time=19412.278..19412.344 rows=100 loops=1)"
" Buffers: shared hit=12521 read=287185"
" -> Sort (cost=690932.94..698624.09 rows=3076458 width=8) (actual time=19412.274..19412.293 rows=100 loops=1)"
" Sort Key: A.a_id"
" Sort Method: top-N heapsort Memory: 29kB"
" Buffers: shared hit=12521 read=287185"
" -> Hash Join (cost=34731.92..573352.93 rows=3076458 width=8) (actual time=822.673..19052.607 rows=417486 loops=1)"
" Hash Cond: (A.b_id = BB.b_id)"
" Buffers: shared hit=12521 read=287185"
" -> Seq Scan on A (cost=0.00..432736.38 rows=7512005 width=16) (actual time=0.044..9306.081 rows=7512001 loops=1)"
" Filter: (((date_one >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_one <= '2019-01-08 03:07:06-05'::timestamp with time zone)) OR ((date_two >= '2012-01-07 04:05:06-05'::timestamp with time zone) AND (date_two <= '2019-01-08 03:05:06-05'::timestamp with time zone)))"
" Rows Removed by Filter: 18"
" Buffers: shared hit=12198 read=270298"
" -> Hash (cost=24997.52..24997.52 rows=778752 width=8) (actual time=820.550..820.550 rows=377405 loops=1)"
" Buckets: 131072 Batches: 1 Memory Usage: 14743kB"
" Buffers: shared hit=323 read=16887"
" -> Seq Scan on fs_invoice_quote invoicequo0_ (cost=0.00..24997.52 rows=778752 width=8) (actual time=0.007..471.024 rows=777884 loops=1)"
" Buffers: shared hit=323 read=16887"
"Total runtime: 19412.432 ms"
Please suggest.
postgresql join order-by
postgresql join order-by
edited Jan 27 at 11:35
McNets
15.9k42061
asked Jan 26 at 23:54
Maksim KaratkouMaksim Karatkou
112
edited Jan 27 at 11:35
McNets
15.9k42061
asked Jan 26 at 23:54
Maksim KaratkouMaksim Karatkou
112
edited Jan 27 at 11:35
McNets
15.9k42061
edited Jan 27 at 11:35
McNets
15.9k42061
edited Jan 27 at 11:35
McNets
15.9k42061
15.9k42061
asked Jan 26 at 23:54
Maksim KaratkouMaksim Karatkou
112
asked Jan 26 at 23:54
Maksim KaratkouMaksim Karatkou
112
asked Jan 26 at 23:54
Maksim KaratkouMaksim Karatkou
112
112
Have you tried applying indexes to the date columns, and rewriting this as aUNIONquery?
– Randolph West
Jan 27 at 0:29
Isdate_twoin table A or B?
– ypercubeᵀᴹ
Jan 27 at 0:55
Hi @Randolph, could you please provide more details re. UNION approach and how that could be helpful to tackle order by issue?
– Maksim Karatkou
Jan 27 at 1:47
the date_two is in table A (updated the question)
– Maksim Karatkou
Jan 27 at 1:48
1
Can you add theCREATE TABLEstatements for both tables? Is there a foreign key fromA (b_id) REFERENCES B (b_id)?
– ypercubeᵀᴹ
Jan 27 at 12:29
|
show 5 more comments
Have you tried applying indexes to the date columns, and rewriting this as aUNIONquery?
– Randolph West
Jan 27 at 0:29
Isdate_twoin table A or B?
– ypercubeᵀᴹ
Jan 27 at 0:55
Hi @Randolph, could you please provide more details re. UNION approach and how that could be helpful to tackle order by issue?
– Maksim Karatkou
Jan 27 at 1:47
the date_two is in table A (updated the question)
– Maksim Karatkou
Jan 27 at 1:48
1
Can you add theCREATE TABLEstatements for both tables? Is there a foreign key fromA (b_id) REFERENCES B (b_id)?
– ypercubeᵀᴹ
Jan 27 at 12:29
Have you tried applying indexes to the date columns, and rewriting this as a
UNION query?– Randolph West
Jan 27 at 0:29
Have you tried applying indexes to the date columns, and rewriting this as a
UNION query?– Randolph West
Jan 27 at 0:29
Is
date_two in table A or B?– ypercubeᵀᴹ
Jan 27 at 0:55
Is
date_two in table A or B?– ypercubeᵀᴹ
Jan 27 at 0:55
Hi @Randolph, could you please provide more details re. UNION approach and how that could be helpful to tackle order by issue?
– Maksim Karatkou
Jan 27 at 1:47
Hi @Randolph, could you please provide more details re. UNION approach and how that could be helpful to tackle order by issue?
– Maksim Karatkou
Jan 27 at 1:47
the date_two is in table A (updated the question)
– Maksim Karatkou
Jan 27 at 1:48
the date_two is in table A (updated the question)
– Maksim Karatkou
Jan 27 at 1:48
1
1
Can you add the
CREATE TABLE statements for both tables? Is there a foreign key from A (b_id) REFERENCES B (b_id) ?– ypercubeᵀᴹ
Jan 27 at 12:29
Can you add the
CREATE TABLE statements for both tables? Is there a foreign key from A (b_id) REFERENCES B (b_id) ?– ypercubeᵀᴹ
Jan 27 at 12:29
|
show 5 more comments
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228167%2fpostgres-slow-query-order-by%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f228167%2fpostgres-slow-query-order-by%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Have you tried applying indexes to the date columns, and rewriting this as a
UNIONquery?– Randolph West
Jan 27 at 0:29
Is
date_twoin table A or B?– ypercubeᵀᴹ
Jan 27 at 0:55
Hi @Randolph, could you please provide more details re. UNION approach and how that could be helpful to tackle order by issue?
– Maksim Karatkou
Jan 27 at 1:47
the date_two is in table A (updated the question)
– Maksim Karatkou
Jan 27 at 1:48
1
Can you add the
CREATE TABLEstatements for both tables? Is there a foreign key fromA (b_id) REFERENCES B (b_id)?– ypercubeᵀᴹ
Jan 27 at 12:29