Hello Jeff,
Thank you again for your advice.
I did indeed think of the ARRAY_AGG() version of the query.
Although this method is very fast (and does use indexes) for smallish array
sizes, this is sadly not practical in my case because the arrays of
matching rows can reach multiple hundreds of tho
On Mon, May 23, 2022 at 3:57 AM Mickael van der Beek <
mickael.van.der.b...@gmail.com> wrote:
> Hello Jeff,
>
> Sadly, the query you suggested won't work because you are only returning
> the first row of the matching inner query rows.
>
Sure, but the query I replaced did the same thing. (I thoug
Hello Jeff,
Sadly, the query you suggested won't work because you are only returning
the first row of the matching inner query rows.
Example:
SELECT
> u.idx,
> u.page_idxs
> FROM
> (
> VALUES
> (1, ARRAY[11, 21, 31]),
> (2, ARRAY[12, 21, 32]),
> (3, ARRAY[13, 23, 31])
On Fri, May 20, 2022 at 6:42 AM Mickael van der Beek <
mickael.van.der.b...@gmail.com> wrote:
>
> Query:
>
> EXPLAIN (
>> ANALYZE,
>> VERBOSE,
>> COSTS,
>> BUFFERS,
>> TIMING
>> )
>> SELECT
>> fu.w2_page_idxs
>> FROM
>> fact_users
>> AS fu
>> WHERE
>> EXISTS (
>> SELECT
>>
Hello Jeff,
Sorry for the delay, here are the EXPLAIN ANALYSE results for one single
row in the inner-query:
Nested Loop Semi Join (cost=1000993.81..10004731160.70 rows=536206
> width=28) (actual time=93765.182..93765.183 rows=0 loops=1)
> Output: fu.w2_page_idxs
> Join Filter: (fu.w2_pa
Hello Jeff,
I have waited a few hours without the query ever finishing which is the
reason I said "never finishes".
Especially because the INNER JOIN version finishes within a few minutes
while being combinatorial and less efficient.
The query probably only does sequential scans.
You will find th
On Wed, Apr 27, 2022 at 8:19 AM Mickael van der Beek <
mickael.van.der.b...@gmail.com> wrote:
>
> The last query does not finish after waiting for more than 15 minutes.
> (The temporary view creation is very fast and required due to the same
> query in a CTE greatly reducing performance (by more t
