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
> 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
> 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 *
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
> 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
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
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
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
"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
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
>
> 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
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
17 matches
Mail list logo