Guys,
Thanks for trying and opening your mind.
If you want to know how Oracle addressed this issue, here it is: index
on two columns. I remember that they told me in the training postgres has
no this kind of index, can someone clarify?
Thanks much!
Xuefeng Zhu (Sherry)
Crown Consulting Inc. -- Oracle DBA
AIM Lab Data Team
(703) 925-3192
Joel Jacobson <j...@gluefinance.com>
04/06/2010 06:30 PM
To
Sherry CTR Zhu/AWA/CNTR/f...@faa, pgsql-performance@postgresql.org
cc
Robert Haas <robertmh...@gmail.com>
Subject
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal
index
Actually, swapping the order of the conditions did in fact make some
difference, strange.
I ran the query a couple of times for each variation to see if the
difference in speed was just a coincidence or a pattern. Looks like the
speed really is different.
EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =
108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1384.401..1384.402
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual
time=1384.399..1384.399 rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1384.431 ms
(4 rows)
EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =
'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1710.166..1710.167
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual
time=1710.164..1710.164 rows=1 loops=1)
Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108))
Total runtime: 1710.200 ms
(4 rows)
EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =
108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1366.526..1366.527
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual
time=1366.525..1366.525 rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1366.552 ms
(4 rows)
EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =
'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1685.395..1685.396
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual
time=1685.394..1685.394 rows=1 loops=1)
Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108))
Total runtime: 1685.423 ms
(4 rows)
EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =
108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1403.904..1403.905
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual
time=1403.903..1403.903 rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1403.931 ms
(4 rows)
EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =
'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1689.014..1689.014
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual
time=1689.012..1689.012 rows=1 loops=1)
Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108))
Total runtime: 1689.041 ms
(4 rows)
EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =
108) AND (Currency = 'SEK')) ORDER BY TransactionID LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1378.322..1378.323
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual
time=1378.320..1378.320 rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1378.349 ms
(4 rows)
EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =
'SEK') AND (AccountID = 108)) ORDER BY TransactionID LIMIT 1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..116.02 rows=1 width=4) (actual time=1696.830..1696.831
rows=1 loops=1)
-> Index Scan using transactions_pkey on transactions
(cost=0.00..1260254.03 rows=10862 width=4) (actual
time=1696.828..1696.828 rows=1 loops=1)
Filter: ((currency = 'SEK'::bpchar) AND (accountid = 108))
Total runtime: 1696.858 ms
(4 rows)
2010/4/6 <sherry.ctr....@faa.gov>
I mean the time you spent on prune which one is cheaper might be another
cost.
Thanks much!
Xuefeng Zhu (Sherry)
Crown Consulting Inc. -- Oracle DBA
AIM Lab Data Team
(703) 925-3192
Sherry CTR Zhu/AWA/CNTR/FAA
AJR-32, Aeronautical Information Mgmt Group
04/06/2010 03:13 PM
To
Robert Haas <robertmh...@gmail.com>
cc
Joel Jacobson <j...@gluefinance.com>
Subject
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less
optimal indexLink
Have you tried before?
Thanks much!
Xuefeng Zhu (Sherry)
Crown Consulting Inc. -- Oracle DBA
AIM Lab Data Team
(703) 925-3192
Robert Haas <robertmh...@gmail.com>
04/06/2010 03:07 PM
To
Sherry CTR Zhu/AWA/CNTR/f...@faa
cc
Joel Jacobson <j...@gluefinance.com>
Subject
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less
optimal index
On Tue, Apr 6, 2010 at 3:05 PM, <sherry.ctr....@faa.gov> wrote:
Just curious,
Switch the where condition to try to make difference.
how about change
((accountid = 108) AND (currency = 'SEK'::bpchar))
to
( (currency = 'SEK'::bpchar) AND (accountid = 108) ).
In earlier version of Oracle, this was common knowledge that optimizer
took the last condition index to use.
Ignore me if you think this is no sence. I didn't have a time to read
your guys' all emails.
PostgreSQL doesn't behave that way - it guesses which order will be
cheaper.
...Robert
--
Best regards,
Joel Jacobson
Glue Finance
E: j...@gluefinance.com
T: +46 70 360 38 01
Postal address:
Glue Finance AB
Box 549
114 11 Stockholm
Sweden
Visiting address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm
Sweden
