Re: Why search term results different query plan?

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Erol ?z <eroloz(at)esg(dot)com(dot)tr>
Cc: PostgreSQL General List <pgsql-general(at)postgresql(dot)org>
Subject: Re: Why search term results different query plan?
Date: 2001-09-30 01:54:47
Message-ID: 20010930115447.A12033@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Sean Chittenden 2001-09-30 02:05:19 Re: Encoding passwords
Previous Message Erol Öz 2001-09-30 01:18:53 Why search term results different query plan?