As you wrote we have about 400/500 partitions in real life. So time problem is
much smaller, but still it is and in one place of aur application we have
decided to help DB and we're indicating in query exact partition we need. What
pushed me to do this test? Just curiosity I think. After I saw i
On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck <
luisrobe...@siscobra.com.br> wrote:
> If you modify last_user_event_2 to select user and event info in the view,
> and just put there where clause directly on the view which is not joined to
> anything, instead of on the "extra copy" of the users
> If you modify last_user_event_2 to select user and event info in the view,
> and just put there where clause directly on the view which is not joined to
> anything, instead of on the "extra copy" of the users table like you were
> showing previously, I would expect that the performance should
If you modify last_user_event_2 to select user and event info in the view,
and just put there where clause directly on the view which is not joined to
anything, instead of on the "extra copy" of the users table like you were
showing previously, I would expect that the performance should be excellen
> It seems like it should be-
>
> SELECT * FROM users u JOIN last_user_event_1 e USING (user_id,user_group);
> --OR--
> SELECT * FROM last_user_event_2 e;
>
> for them to produce the same result set, since the last_user_event_2 already
> (could) have users info in it very simply by select * i
Was there a reason to exceed 100-500 partitions in real life that pushed
you to do this test? Is there some issue you see when using 100 partitions
that is solved or reduced in severity by increasing to 1200 or 6000
partitions?
It seems like it should be-
SELECT * FROM users u JOIN last_user_event_1 e USING (user_id,user_group);
--OR--
SELECT * FROM last_user_event_2 e;
for them to produce the same result set, since the last_user_event_2
already (could) have users info in it very simply by select * instead of
e.* in that
> The obfuscation makes it difficult to guess at the query you are writing and
> the schema you are using. Can you provide any additional information without
> revealing sensitive info?
>
> 1) Do you have an index on ( user_id ASC, timestamp_inc DESC ) ?
> 2) Sub-queries can't be re-written inl
On Tue, 13 Aug 2019 at 08:03, Piotr Włodarczyk
wrote:
> PostgreSQL11.5:
>
> select on main partition (public.book): planner: 60ms, execution: 5ms
> select on partitioned table (public.book_1-1000): planner: 2.7 ms, execution:
> 2,4 ms
>
> PostgreSQL 12B3:
>
> select on main partition (public.book
The obfuscation makes it difficult to guess at the query you are writing
and the schema you are using. Can you provide any additional information
without revealing sensitive info?
1) Do you have an index on ( user_id ASC, timestamp_inc DESC ) ?
2) Sub-queries can't be re-written inline by the opti
Hey guys,
So I have two tables: users and events. It is very common for my
application to request the last user event.
Usually, what I'll do is get the user, and then SELECT * from events
WHERE user_id = :user order by timestamp_inc desc LIMIT 1.
I have a big problem, however:
My app uses
Thanks for clarifying your position and sharing the results you have seen.
That is impressive indeed.
It seems likely that waiting for v12 is needed since feature are not back
patched. Perhaps one of the contributors will confirm, but that is my
expectation.
@Michael Lewis: I know documentation. I'm just considerations about possible
performance tricks in current production version. I've tested this on V12 on
another computer and I can say that I'm impressed. I've checked on 1200
partitions and times are:
PostgreSQL11.5:
• select on main partition
Queries against tables with a lot of partitions (> 1000) start to incur
an increasing planning time duration even with the current version,
V11. V12 purportedly has fixed this problem, allowing thousands of
partitioned tables without a heavy planning cost. Can't seem to find
the threads on th
"It is also important to consider the overhead of partitioning during query
planning and execution. The query planner is generally able to handle
partition hierarchies with *up to a few hundred partitions fairly well*,
provided that typical queries allow the query planner to prune all but a
small n
Hello,
We have partitioned tables in two levels. Both stages are partitioned in
ranges method. We see that planner and executor time was 10 time slower when
we asked main table rather than partitioned. My question is did planner and
executor are working optimal? I have doubts about it. Let's consi
>
> Presumably I could partition proposal on has_been_anonymised, however
> the row counts seem low enough that it feels a bit like overkill? We
> also need referential integrity so I'll need to wait until that's in
> (I think it's coming in PG12?)
>
> If I decrease the number of legacy_organisatio
Aha!
That's a great hint, we had that set down to an obscenely low value
due to our max_connections setting being quite high. I've tweaked it
back up to 4MB for now and it's definitely had a marked improvement!
Many Thanks,
Rob
On 09/08/2019, Jeff Janes wrote:
> On Fri, Aug 9, 2019 at 4:42 AM R
18 matches
Mail list logo
