From: | Johannes <jotpe(at)posteo(dot)de> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: update inside function does not use the index |
Date: | 2015-11-16 21:06:40 |
Message-ID: | 564A4560.8010304@posteo.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
That solves my problem. Thanks!!
Best regards Johannes
Am 16.11.2015 um 18:19 schrieb Tom Lane:
> Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> writes:
>> On 11/16/2015 08:03 AM, Johannes wrote:
>>>> In every loop I execute an update with a where LIKE condition, which
>>>> relates to my current cursor position:
>>>> FOR i IN SELECT id, level_ids, path_names||'%' as path_names from x LOOP
>>>> update x set path_ids[i.level] = id where path_names like i.path_names;
>
> Probably the problem is that the planner is unable to fold i.path_names
> to a constant, so it can't derive an indexscan condition from the LIKE
> clause.
>
> A little bit of experimentation says that that will work if "i" is
> declared with a named rowtype, but not if it's declared RECORD. This
> might or might not be something we could fix, but in the meantime I'd
> try
>
> DECLARE i x%rowtype;
>
> FOR i IN SELECT * FROM x LOOP
> update x set path_ids[i.level] = id where path_names like (i.path_names || '%');
>
> which while it might look less "constant" is actually more so from the
> planner's perspective, because there is no question of whether "i" has
> got a field of that name.
>
> regards, tom lane
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2015-11-16 21:16:28 | Re: Importing directly from BCP files |
Previous Message | Kevin Grittner | 2015-11-16 20:49:14 | Re: Importing directly from BCP files |