On Jun 23, Chris Wilson modulated:
> ...
> create table metric_pos (id serial primary key, pos integer);
> create index idx_metric_pos_id_pos on metric_pos (id, pos);
> ...
> create table asset_pos (id serial primary key, pos integer);
> ...
Did you only omit a CREATE INDEX statement o
Clint Miller writes:
> That's a good plan because it's not doing a quick sort. Instead, it's just
> reading the sort order off of the index, which is exactly what I want. (I
> had to disable enable_sort because I didn't have enough rows of test data
> in the table to get Postgres to use the index.
On Fri, Jun 23, 2017 at 3:58 PM, Clint Miller wrote:
> Here, it's loading the full result set into memory and doing a quick sort.
> (I think that's what it's doing, at least. If that's not the case, let me
> know.) That's not good.
It's not sorting stuff that doesn't need to be read into memory i
Let's say I have the following table and index:
create table foo(s text, i integer);
create index foo_idx on foo (s, i);
If I run the following commands:
start transaction;
set local enable_sort = off;
explain analyze select * from foo where s = 'a' order by i;
end;
I get the following query pl
Dear pgsql-performance list,
I think I've found a case where the query planner chooses quite a
suboptimal plan for joining three tables. The main "fact" table
(metric_value) links to two others with far fewer rows (like an OLAP/star
design). We retrieve and summarise a large fraction of rows from
ditto here... much slower, and crashes too often. We run an evergreen shop
where I work, but everyone has moved back to III.
Sent from my BlackBerry KEYone - the most secure mobile device
From: adambrusselb...@gmail.com
Sent: June 23, 2017 8:11 AM
To: t...@sss.pgh.pa.us
Cc: sumeet.k.shu...@gmail.
Akihiko Odaki wrote:
> On 2017-06-23 20:20, Albe Laurenz wrote:
>> You could either try to do something like
>>
>> SELECT *
>> FROM (SELECT "posts".*
>>FROM "posts"
>> JOIN "follows" ON "follows"."target_account" = "posts"."account"
>>WHERE "follows"."owner_account" = $1
>
On Fri, Jun 23, 2017 at 12:50 AM, Tom Lane wrote:
>
> It's possible that pgAdmin4 has improved matters in this area.
>
Sadly, not in my experience. It's actually considerably worse than
pgAdminIII in my experience when selecting a lot of rows, especially when
very wide (20+ columns).
On 2017-06-23 20:20, Albe Laurenz wrote:
You could either try to do something like
SELECT *
FROM (SELECT "posts".*
FROM "posts"
JOIN "follows" ON "follows"."target_account" =
"posts"."account"
WHERE "follows"."owner_account" = $1
OFFSET 0) q
ORDER BY "posts"."ti
Thank you for your quick reply. Your solution works for me!
On 2017-06-23 20:20, Albe Laurenz wrote:
> PostgreSQL`s plan is to use the index on "posts"."timestamp" to find the
> rows with the lowest "timestamp", match with rows from "posts" in
> a nested loop and stop as soon as it has found 100
Akihiko Odaki wrote:
> I am having a problem with nested loop join.
>
> A database has 2 tables: "posts" and "follows".
> Table "posts" have two columns: "timestamp" and "account".
> Table "follows" have two columns: "target_account" and "owner_account".
> The database also has an index on "posts"
Hi all,
I am having a problem with nested loop join.
A database has 2 tables: "posts" and "follows".
Table "posts" have two columns: "timestamp" and "account".
Table "follows" have two columns: "target_account" and "owner_account".
The database also has an index on "posts" ("account", "timestamp
>From: Tom Lane
>To: Sumeet Shukla
>Cc: Dave Stibrany ; pgsql-performance@postgresql.org
>Sent: Friday, 23 June 2017, 5:50
>Subject: Re: [PERFORM] Dataset is fetched from cache but still takes same time
>to fetch records as first run
> Sumeet Shukla writes:>
>> Yes, but when I actually execute
13 matches
Mail list logo
