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=1 loops=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) (actual time=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.023 rows=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
