Re: [PERFORM] Inlining of functions (doing LIKE on an array)

Thanks for the pointer on the "select exists" syntax Tom. Much appreciated. I couldn't figure it out! And as for normalizing, yes, thought about it, but the one-to-many relationship would make other scenarios we have more complex and slower. So I am juggling with trade-offs. So, here are my

Re: [PERFORM] Inlining of functions (doing LIKE on an array)

On Thu, Nov 10, 2016 at 10:54 PM, l...@laurent-hasson.com < l...@laurent-hasson.com> wrote: > Hello, > > > > I am trying to implement an efficient “like” over a text[]. I see a lot of > people have tried before me and I learnt a lot through the forums. > Have you looked at parray_gin?

Re: [PERFORM] Inlining of functions (doing LIKE on an array)

"l...@laurent-hasson.com" writes: > I tried "exists", but won't work in the Function, i.e., > CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS bigint > AS 'exists (select * from unnest($1) a where a like $2)' > LANGUAGE SQL STRICT IMMUTABLE LEAKPROOF Syntax

Re: [PERFORM] Any advice tuning this query ?

I have a couple of suggestions which should lead to some minor improvements, but in general I am surprised by the huge size of the result set. Is your goal really to get a 43 million row result? When a query returns that many rows usually all possible query plans are more or less bad. 1) You

Re: [PERFORM] Inlining of functions (doing LIKE on an array)

I tried "exists", but won't work in the Function, i.e., CREATE OR REPLACE FUNCTION ArrayLike(text[], text) RETURNS bigint AS 'exists (select * from unnest($1) a where a like $2)' LANGUAGE SQL STRICT IMMUTABLE LEAKPROOF It's as expected though. As for the GIN indices, I tried and it didn't

Re: [PERFORM] Any advice tuning this query ?

Hi, On Fri, 2016-11-11 at 16:19 +0100, Henrik Ekenberg wrote: > Sort Method: external merge  Disk: 16782928kB This query is generating 16GB temp file on disk. Is this the amount of data you want to sort? Regards, -- Devrim GÜNDÜZ EnterpriseDB: http://www.enterprisedb.com PostgreSQL

[PERFORM] Any advice tuning this query ?

Hi, I have a select moving around a lot of data and takes times Any advice tuning this query ? EXPLAIN (ANALYZE ON, BUFFERS ON)     select     d.books,     d.date publish_date,     extract(dow from d.date) publish_dow,     week_num_fixed,     coalesce(sum(case when i.invno is not null then 1

Re: [PERFORM] Inlining of functions (doing LIKE on an array)

> From: pgsql-performance-ow...@postgresql.org > [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of > l...@laurent-hasson.com > Sent: Freitag, 11. November 2016 07:54 > To: pgsql-performance@postgresql.org > Subject: [PERFORM] Inlining of functions (doing LIKE on an array) > >