Hi All
I did final research and saw that the difference between best and worst
cases is indeed really small.
I want to thank you guys for your time and efforts.
Best regards.
вт, 2 нояб. 2021 г. в 18:04, Pavel Borisov :
> вт, 2 нояб. 2021 г. в 16:04, Hayk Manukyan :
>
>> Tomas Vondra
>> > Are
вт, 2 нояб. 2021 г. в 16:04, Hayk Manukyan :
> Tomas Vondra
> > Are you suggesting those are not the actual best/worst cases and we
> > should use some other indexes? If yes, which ones?
>
> I would say yes.
> In my case I am not querying only sequence column.
> I have the following cases which I
On 11/2/21 13:04, Hayk Manukyan wrote:
Tomas Vondra
> Are you suggesting those are not the actual best/worst cases and we
> should use some other indexes? If yes, which ones?
I would say yes.
In my case I am not querying only sequence column.
I have the following cases which I want to optim
Tomas Vondra
> Are you suggesting those are not the actual best/worst cases and we
> should use some other indexes? If yes, which ones?
I would say yes.
In my case I am not querying only sequence column.
I have the following cases which I want to optimize.
1. Select * from Some_table where job =
On 11/1/21 21:06, Robert Haas wrote:
On Tue, Oct 26, 2021 at 11:11 AM Tomas Vondra
wrote:
If I get what you propose, you want to have a "top" tree for (job, nlp,
year), which "splits" the data set into subsets of ~5000-7000 rows. And
then for each subset you want a separate "small" trees on
On Tue, Oct 26, 2021 at 11:11 AM Tomas Vondra
wrote:
> If I get what you propose, you want to have a "top" tree for (job, nlp,
> year), which "splits" the data set into subsets of ~5000-7000 rows. And
> then for each subset you want a separate "small" trees on each of the
> other columns, so in th
On 11/1/21 1:24 PM, Hayk Manukyan wrote:
> I agree with the above mentioned.
> The only concern I have is that we compare little wrong things.
> For read we should compare
> (job, nlp, year, sequence) AND (job, nlp, year, Scan_ID) and (job, nlp,
> year, issue_flag ) VS (job, nlp, year, sequ
I agree with the above mentioned.
The only concern I have is that we compare little wrong things.
For read we should compare
(job, nlp, year, sequence) AND (job, nlp, year, Scan_ID) and (job, nlp,
year, issue_flag ) VS (job, nlp, year, sequence, Scan_ID, issue_flag)
OR (job, nlp, year INCLUDE(
On 10/31/21 16:48, Pavel Borisov wrote:
4 columns: 106 ms
6 columns: 109 ms
So there's like 3% difference between the two cases, and even that
might
be just noise. This is consistent with the two indexes being about the
same size.
I also don't think we can get g
>
> 4 columns: 106 ms
> 6 columns: 109 ms
>
> So there's like 3% difference between the two cases, and even that might
> be just noise. This is consistent with the two indexes being about the
> same size.
>
I also don't think we can get great speedup in the mentioned case, so it is
not urgently
On 10/29/21 15:32, Hayk Manukyan wrote:
Hi all
First of all thank you all for fast and rich responses, that is really nice.
I don't have that deep knowledge of how postgres works under the hood
so I will try to explain more user side.
I want to refer for some points mentioned above.
- Firs
Hi all
First of all thank you all for fast and rich responses, that is really nice.
I don't have that deep knowledge of how postgres works under the hood so I
will try to explain more user side.
I want to refer for some points mentioned above.
- First INCLUDE statement mostly eliminates the neces
On Wed, Oct 27, 2021 at 1:02 AM Pavel Borisov wrote:
> AFAIK Gin is lossy for phrase queries as we don't store word position in the
> posting list. For purely logical queries, where position doesn't matter, it's
> not lossy.
GIN is always lossy, in the sense that it provides only a
gingetbitmap
>
> It's no coincidence that GIN indexes (which
> have some of the same issues) only support lossy index scans.
>
AFAIK Gin is lossy for phrase queries as we don't store word position in
the posting list. For purely logical queries, where position doesn't
matter, it's not lossy.
One more considera
On Tue, Oct 26, 2021 at 3:45 PM Tom Lane wrote:
> Of course, we have that today from the solution of one index with the
> extra columns "included". I think the OP has completely failed to make
> any case why that's not a good enough approach.
I think that the design that the OP is talking about
Mark Dilger writes:
> For three separate indexes, an update or delete of a single row in the
> indexed table would surely require changing at least three pages in the
> indexes. For some as-yet-ill-defined combined index type, perhaps the three
> entries in the index would fall on the same ind
> On Oct 26, 2021, at 1:43 PM, Tomas Vondra
> wrote:
>
> I'm still rather skeptical about it - for such feature to be useful the
> prefix columns must not be very selective, i.e. the posting trees are
> expected to be fairly large (e.g. 5-7k rows). It pretty much has to to
> require multip
On 10/26/21 21:39, Pavel Borisov wrote:
I've already answered OP but it seems in the wrong thread, so I copy it
here:
I think now in many cases you can effectively use covering index to have
fast index-only scans without index duplication. It will help if you
don't have great selectivity o
I've already answered OP but it seems in the wrong thread, so I copy it
here:
I think now in many cases you can effectively use covering index to have
fast index-only scans without index duplication. It will help if you don't
have great selectivity on the last column (most probably you don't). E.g
On 10/26/21 8:49 AM, Hayk Manukyan wrote:
> ok. here is the deal if I have the following index with 6 column
>
> CREATE INDEX ON job_nlp_year_scan (job, nlp, year, scan_id, issue_flag,
> sequence);
>
> I need to specify all 6 columns in where clause in order to fully use
> this index.
What do
ok. here is the deal if I have the following index with 6 column
CREATE INDEX ON job_nlp_year_scan (job, nlp, year, scan_id, issue_flag,
sequence);
I need to specify all 6 columns in where clause in order to fully use this
index.
It will not be efficient in cases when I have 4 condition in where
Hi,
On 10/25/21 16:07, Hayk Manukyan wrote:
Hi everyone. I want to do some feature request regarding indexes, as far as
I know this kind of functionality doesn't exists in Postgres. Here is my
problem :
I need to create following indexes:
Create index job_nlp_year_scan on ingest_scans_s
Hi everyone. I want to do some feature request regarding indexes, as far as
I know this kind of functionality doesn't exists in Postgres. Here is my
problem :
I need to create following indexes:
Create index job_nlp_year_scan on ingest_scans_stageing
(`job`,`nlp`,`year`,`scan_id`);
23 matches
Mail list logo
