Обсуждение: Why the difference in plans ??

Hi,

I have two table: customers and salesorders. salesorders have a foreign
key to the customer

If I run this query:

SELECT
salesOrders.objectid,
salesOrders.ordernumber,
salesOrders.orderdate,
customers.objectid,
customers.customernumber,
customers.lastname
FROM prototype.salesorders
INNER JOIN prototype.customers ON (
customers.objectid = salesorders.customer
)
where
lastname ilike 'Boonk'
order by ordernumber asc LIMIT 1


WITHOUT "LIMIT 1" this query plan is executed (EXPLAIN ANALYZE):


Sort  (cost=41811.90..41812.78 rows=353 width=103) (actual time=623.855..623.867 rows=7 loops=1)
  Sort Key: salesorders.ordernumber
  ->  Nested Loop  (cost=2.15..41796.96 rows=353 width=103) (actual time=0.166..623.793 rows=7 loops=1)
        ->  Seq Scan on customers  (cost=0.00..21429.44 rows=118 width=55) (actual time=0.037..623.325 rows=5 loops=1)
              Filter: (lastname ~~* 'Boonk'::text)
        ->  Bitmap Heap Scan on salesorders  (cost=2.15..172.06 rows=44 width=88) (actual time=0.075..0.079 rows=1
loops=5)
              Recheck Cond: ("outer".objectid = salesorders.customer)
              ->  Bitmap Index Scan on orders_customer  (cost=0.00..2.15 rows=44 width=0) (actual time=0.066..0.066
rows=1loops=5) 
                    Index Cond: ("outer".objectid = salesorders.customer)
Total runtime: 624.051 ms



With the limit this query plan is used (EXPLAIN ANALYZE):

Limit  (cost=0.00..18963.24 rows=1 width=103) (actual time=18404.730..18404.732 rows=1 loops=1)
  ->  Nested Loop  (cost=0.00..6694025.41 rows=353 width=103) (actual time=18404.723..18404.723 rows=1 loops=1)
        ->  Index Scan using prototype_orders_ordernumber on salesorders  (cost=0.00..37263.14 rows=1104381 width=88)
(actualtime=26.715..1862.408 rows=607645 loops=1) 
        ->  Index Scan using pk_prototype_customers on customers  (cost=0.00..6.02 rows=1 width=55) (actual
time=0.023..0.023rows=0 loops=607645) 
              Index Cond: (customers.objectid = "outer".customer)
              Filter: (lastname ~~* 'Boonk'::text)
Total runtime: 18404.883 ms


Both tables are freshly fully vacuumed analyzed.

Why the difference and can I influence the result so that the first
query plan (which is the fastest) is actually used in both cases (I
would expect that the limit would be done after the sort?)?

TIA

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

"Joost Kraaijeveld" <J.Kraaijeveld@Askesis.nl> writes:
> Why the difference and can I influence the result so that the first
> query plan (which is the fastest) is actually used in both cases (I
> would expect that the limit would be done after the sort?)?

It likes the second plan because 6694025.41/353 < 41812.78.  It would
probably be right, too, if the number of matching rows were indeed 353,
but it seems there are only 7.  Try increasing your statistics target
and re-analyzing.

            regards, tom lane

On Fri, 2006-09-15 at 10:08 -0400, Tom Lane wrote:
> but it seems there are only 7.  Try increasing your statistics target
> and re-analyzing.

Do you mean with "increasing my statistics target" changing the value of
"default_statistics_target = 10" to a bigger number? If so, changing it
to 900 did not make any difference (PostgreSQL restarted, vacuumed
analysed etc).

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl

Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes:
> Do you mean with "increasing my statistics target" changing the value of
> "default_statistics_target = 10" to a bigger number? If so, changing it
> to 900 did not make any difference (PostgreSQL restarted, vacuumed
> analysed etc).

Hm, did the "353" rowcount estimate not change at all?

            regards, tom lane