From: | "Qingqing Zhou" <zhouqq(at)cs(dot)toronto(dot)edu> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: LIKE, leading percent, bind parameters and indexes |
Date: | 2006-05-24 06:16:17 |
Message-ID: | e50tnt$2baj$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote
>
> Yeah. The LIKE index optimization depends on seeing a constant LIKE
> pattern at plan time --- otherwise the planner doesn't know what
> indexscan parameters to generate. So a bound-parameter query loses.
>
AFAICS the problem is not restricted to LIKE, we can easily find a lot of
similar problems caused by the actual parameters. For example, SeqScan vs.
IndexScan vs. BitmapIndexScan for a range query. So an improvement is
definitely needed.
> Ideas for improving this situation are welcome ... it's not an easy
> problem ...
>
IMHO basically we have two ways to get better plan: one is to have a set of
alternative plans for prepare queries. This will add some cost but PREPARE
is supposed to do only once against a lot of EXECUTE. But still, the biggest
problem is that number of plans is not controllable.
Another way is to generate a plan on the fly. What we do is to let some
REPLAN nodes sit on top of some critical plan node: at the execution, we
will compare the actual numbers we get and the estimated number we have
(mabye "rows"?), once we find that a re-plan efforts might be deserved, we
will get a new plan on the fly. In this way, I think a not-too-big patch
will do. I remember there is a paper talking about this somewhere but not
remember clearly. -- This method can handle the range query problem above,
but not for LIKE. So we may have to kludge some code to handle LIKE
especially :-(.
Regards,
Qingqing
From | Date | Subject | |
---|---|---|---|
Next Message | Zeugswetter Andreas DCP SD | 2006-05-24 07:56:05 | Re: error-free disabling of individual child partition |
Previous Message | Tom Lane | 2006-05-24 04:28:02 | Re: Performance Issues |