Re: Why search term results different query plan?

On Sun, Sep 30, 2001 at 04:18:53AM +0300, Erol ?z wrote:
> Hi,
> Could anybody give me hint, or reccomend a source about this:
> Please note that two queries are different only in search terms (STAR and
> A). Plan and performane difference between them confused me.
> Thanks,
> Erol

%STAR%:
> Unique (cost=1599.50..1599.58 rows=1 width=98)
> -> Sort (cost=1599.50..1599.50 rows=2 width=98)
> -> Nested Loop (cost=0.00..1599.48 rows=2 width=98)
> -> Seq Scan on product_t p (cost=0.00..613.41 rows=1 width=94)
> -> Seq Scan on product_detail_t pd (cost=0.00..983.19 rows=231 width=4)

%A%:
> Unique (cost=4456.37..5127.88 rows=1919 width=98)
> -> Sort (cost=4456.37..4456.37 rows=19186 width=98)
> -> Merge Join (cost=2389.21..2496.11 rows=19186 width=98)
> -> Sort (cost=1396.97..1396.97 rows=8321 width=94)
> -> Seq Scan on product_t p (cost=0.00..613.41 rows=8321 width=94)
> -> Sort (cost=992.24..992.24 rows=231 width=4)
> -> Seq Scan on product_detail_t pd (cost=0.00..983.19 rows=231 width=4)

Note how in the first one it assumes that there is only one match in p
whereas in the second there are 8321. Are either of those correct?

I don't really see how you can do any real estimates on %X% type queries,
although maybe it's assuming longer string => less matches.

Not sure how to fix it though...

HTH,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org>
http://svana.org/kleptog/
> Magnetism, electricity and motion are like a three-for-two special offer:
> if you have two of them, the third one comes free.