that slave nodes you only scale
the *read* throughput.
Hope it helps,
Marco Colli
PM Nick Cleaton wrote:
> On Fri, 4 Dec 2020 at 15:39, Marco Colli wrote:
>
>> Hello!
>>
>> We have a multi-tenant service where each customer has millions of users
>> (total: ~150M rows). Now we would like to let each customer define some
>> custom columns for
dex-wildcard/).
Are there any plans to add support for range queries to GIN indexes (on
JSON) in the future versions of PostgreSQL?
Marco Colli
Pushpad
thousands to
a few millions of rows and each project has its own tags that the customer
can define (unlimited tags for each row, but usually only 1 - 10 actually
used)
Il Dom 2 Feb 2020, 19:32 Tom Lane ha scritto:
> Marco Colli writes:
> > Unfortunately I don't get actual improve
?
On Sun, Feb 2, 2020 at 6:11 PM Tom Lane wrote:
> Marco Colli writes:
> > Let's say that you have a simple query like the following on a large
> table
> > (for a multi-tenant application):
> > SELECT "subscribers".* FROM "subscribers" WHERE
>
zby wrote:
> On Sun, Feb 02, 2020 at 03:18:19PM +0100, Marco Colli wrote:
> > Hello!
> >
> > Let's say that you have a simple query like the following on a large
> table
> > (for a multi-tenant application):
> > SELECT "subscribers".* FROM &quo
s... but this is a further step. Currently I can create statistics,
however it seems to have no positive effect on the estimates for the
above case
Marco Colli
ws on disk... It cannot take ~1 minute to access a few rows on disk (max
30 rows, actual 0 rows).
On Fri, Jan 10, 2020 at 4:18 PM Tom Lane wrote:
> Marco Colli writes:
> > As you can see it is a *index scan* and not an *index only* scan... I
> don't
> > understand why.
y the query... so
an index only scan should be possible.
On Fri, Jan 10, 2020 at 2:34 PM Justin Pryzby wrote:
> On Fri, Jan 10, 2020 at 12:03:39PM +0100, Marco Colli wrote:
> > I have added this index which would allow an index only scan:
> > "index_subscriptions_on_project_i
ll on array fields like tags??).
On Fri, Jan 10, 2020 at 4:06 AM Justin Pryzby wrote:
> On Fri, Jan 10, 2020 at 02:11:14AM +0100, Marco Colli wrote:
> > I have a query on a large table that is very fast (0s):
> >
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff2
eated_at DESC)
On Fri, Jan 10, 2020 at 4:06 AM Justin Pryzby wrote:
> On Fri, Jan 10, 2020 at 02:11:14AM +0100, Marco Colli wrote:
> > I have a query on a large table that is very fast (0s):
> >
> https://gist.github.com/collimarco/039412b4fe0dcf39955888f96eff29db#file-fast_que
ry is generated dynamically by customers of a SaaS, so I don't
have full control on it
Thank you very much for any advice!
Marco Colli
Wow! Thank you very much Jeff!! I am really grateful.
Thanks to the btree (instead of gin) the query now takes about 500ms
instead of 70s.
Il Mer 13 Nov 2019, 13:18 Jeff Janes ha scritto:
> On Wed, Nov 13, 2019 at 6:56 AM Marco Colli
> wrote:
>
>> > the answer is that is b
e same index with BTree, because PG
doesn't support BTree on array :(
On Wed, Nov 13, 2019 at 12:30 PM Jeff Janes wrote:
> On Wed, Nov 13, 2019 at 4:20 AM Marco Colli
> wrote:
>
>> Replying to the previous questions:
>> - work_mem = 64MB (there are hundreds of connections
Replying to the previous questions:
- work_mem = 64MB (there are hundreds of connections)
- the project 123 has more than 7M records, and those that don't have the
tag 'en' are 4.8M
> What was the plan for the one that took 500ms?
This is the query / plan without the filter on tags:
SELECT COU
HAVE the tag (~7s)
3. calculate [bitmap 1] - [bitmap 2] to find the subscriptions of the
project that DON'T HAVE the tag
On Tue, Nov 12, 2019 at 9:50 PM Tom Lane wrote:
> Marco Colli writes:
> > 3) Here's the query plan that I get after disabling the seq scan:
>
> >
1) It is running on a DigitalOcean CPU-optimized droplet with dedicated
hyperthreads (16 cores) and SSD.
SHOW random_page_cost; => 2
2) What config names should I check exactly? I used some suggestions from
the online PGTune, when I first configured the db some months ago:
max_worker_processes = 1
To be honest, I have simplified the question above. In order to show you
the plan, I must show you the actual query, which is this:
=== QUERY ===
SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" =
123 AND "subscriptions"."trashed_at" IS NULL AND NOT (tags @>
ARRAY['en']::va
7;t1' = ANY (tags));
How can I make the "not in array" operation fast?
Any help would be appreciated, thank you!
Marco Colli
PostgreSQL 11 on Ubuntu 18LTS
I have completely solved (from 17s to 1s) by running this command:
vacuum analyze subscriptions;
Now I run the autovacuum more frequently using these settings in
postgresql.conf:
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.01
Thanks to everyone - and in particular to
Hello!
Any help would be greatly appreciated.
I need to run these simple queries on a table with millions of rows:
```
SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" =
123;
```
```
SELECT COUNT(*) FROM "subscriptions" WHERE "subscriptions"."project_id" =
123 AND "subscri
21 matches
Mail list logo
