Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
) - 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 indexLinkNotes:///852576860052CAFA/DABA975B9FB113EB852564B5001283EA/15C2483F84B5A6D0852576FD006911AC 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*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
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
Hi Xuefeng, You have misunderstood the problem. The index used in the query not containing the LIMIT 1 part, is index_transactions_accountid_currency, which is indeed a two column index. The problem is this index is not used when using LIMIT 1. 2010/4/7 sherry.ctr@faa.gov 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
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
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
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
On Wed, Apr 7, 2010 at 1:20 PM, sherry.ctr@faa.gov wrote: 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? lies. postgresql allows you indices on multiple columns. What it does not have, is index on multiple tables. -- GJ
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
Do you mean one index on two columns? something like this: create index idx1 on tb1(col1, col2); Thanks much! Xuefeng Zhu (Sherry) Crown Consulting Inc. -- Oracle DBA AIM Lab Data Team (703) 925-3192 Grzegorz Jaśkiewicz gryz...@gmail.com 04/07/2010 08:51 AM To Sherry CTR Zhu/AWA/CNTR/f...@faa cc Joel Jacobson j...@gluefinance.com, pgsql-performance@postgresql.org, Robert Haas robertmh...@gmail.com Subject Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index On Wed, Apr 7, 2010 at 1:20 PM, sherry.ctr@faa.gov wrote: 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? lies. postgresql allows you indices on multiple columns. What it does not have, is index on multiple tables. -- GJ
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
2010/4/7 sherry.ctr@faa.gov Do you mean one index on two columns? something like this: create index idx1 on tb1(col1, col2); yup :) It would be quite useless without that feature. Don't listen to oracle folks, they obviously know not much about products others than oracle db(s). -- GJ
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
Please just let me know if Postgres can do this kind of index or not. create index idx1 on tb1(col1, col2) Then later we can find it is useful or useless. Thanks much! Xuefeng Zhu (Sherry) Crown Consulting Inc. -- Oracle DBA AIM Lab Data Team (703) 925-3192 Grzegorz Jaśkiewicz gryz...@gmail.com Sent by: pgsql-performance-ow...@postgresql.org 04/07/2010 09:12 AM To Sherry CTR Zhu/AWA/CNTR/f...@faa cc Joel Jacobson j...@gluefinance.com, pgsql-performance@postgresql.org, Robert Haas robertmh...@gmail.com Subject Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index 2010/4/7 sherry.ctr@faa.gov Do you mean one index on two columns? something like this: create index idx1 on tb1(col1, col2); yup :) It would be quite useless without that feature. Don't listen to oracle folks, they obviously know not much about products others than oracle db(s). -- GJ
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
On Wed, 7 Apr 2010, sherry.ctr@faa.gov wrote: Please just let me know if Postgres can do this kind of index or not. create index idx1 on tb1(col1, col2) Then later we can find it is useful or useless. Have you tried it? Grzegorz Jaśkiewicz gryz...@gmail.com wrote: something like this: create index idx1 on tb1(col1, col2); yup :) For those of you who are not native English speakers, Yup is a synonym for Yes. Matthew -- Richards' Laws of Data Security: 1. Don't buy a computer. 2. If you must buy a computer, don't turn it on. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
I came across a strange problem when writing a plpgsql function.
Why won't the query planner realize it would be a lot faster to use the
index_transactions_accountid_currency index instead of using the
transactions_pkey index in the queries below?
The LIMIT 1 part of the query slows it down from 0.07 ms to 1023 ms.
Is this a bug? I'm using version 8.4.1.
db=# SELECT TransactionID FROM Transactions WHERE AccountID = 108 AND
Currency = 'SEK' ORDER BY TransactionID;
transactionid
---
2870130
2870164
3371529
3371545
3371565
(5 rows)
db=# EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE AccountID
= 108 AND Currency = 'SEK' ORDER BY TransactionID;
QUERY PLAN
--
Sort (cost=27106.33..27134.69 rows=11345 width=4) (actual
time=0.048..0.049 rows=5 loops=1)
Sort Key: transactionid
Sort Method: quicksort Memory: 25kB
- Bitmap Heap Scan on transactions (cost=213.39..26342.26 rows=11345
width=4) (actual time=0.033..0.039 rows=5 loops=1)
Recheck Cond: ((accountid = 108) AND (currency = 'SEK'::bpchar))
- Bitmap Index Scan on index_transactions_accountid_currency
(cost=0.00..210.56 rows=11345 width=0) (actual time=0.027..0.027 rows=5
loops=1)
Index Cond: ((accountid = 108) AND (currency =
'SEK'::bpchar))
Total runtime: 0.070 ms
(8 rows)
db=# SELECT TransactionID FROM Transactions WHERE AccountID = 108 AND
Currency = 'SEK' ORDER BY TransactionID LIMIT 1;
transactionid
---
2870130
(1 row)
db=# EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE AccountID
= 108 AND Currency = 'SEK' ORDER BY TransactionID LIMIT 1;
QUERY
PLAN
-
Limit (cost=0.00..43.46 rows=1 width=4) (actual time=1023.213..1023.214
rows=1 loops=1)
- Index Scan using transactions_pkey on transactions
(cost=0.00..493029.74 rows=11345 width=4) (actual time=1023.212..1023.212
rows=1 loops=1)
Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar))
Total runtime: 1023.244 ms
(4 rows)
db=# \d transactions
Table public.transactions
Column | Type |
Modifiers
---+--+---
transactionid | integer | not null default
nextval('seqtransactions'::regclass)
eventid | integer | not null
ruleid| integer | not null
accountid | integer | not null
amount| numeric | not null
balance | numeric | not null
currency | character(3) | not null
recorddate| timestamp with time zone | not null default
now()
Indexes:
transactions_pkey PRIMARY KEY, btree (transactionid)
index_transactions_accountid_currency btree (accountid, currency)
index_transactions_eventid btree (eventid)
Foreign-key constraints:
transactions_accountid_fkey FOREIGN KEY (accountid) REFERENCES
accounts(accountid) DEFERRABLE
transactions_eventid_fkey FOREIGN KEY (eventid) REFERENCES
events(eventid) DEFERRABLE
transactions_ruleid_fkey FOREIGN KEY (ruleid) REFERENCES rules(ruleid)
DEFERRABLE
--
Best regards,
Joel Jacobson
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
On Fri, Apr 2, 2010 at 2:19 PM, Joel Jacobson j...@gluefinance.com wrote: Is this a bug? I'm using version 8.4.1. It's not really a bug, but it's definitely not a feature either. Limit (cost=0.00..43.46 rows=1 width=4) (actual time=1023.213..1023.214 rows=1 loops=1) - Index Scan using transactions_pkey on transactions (cost=0.00..493029.74 rows=11345 width=4) (actual time=1023.212..1023.212 rows=1 loops=1) Filter: ((accountid = 108) AND (currency = 'SEK'::bpchar)) Total runtime: 1023.244 ms (4 rows) The planner's idea here is that rows matching the filter criteria will be common enough that an index scan over transactions_pkey will find one fairly quickly, at which point the executor can return that row and stop. But it turns out that those rows aren't as common as the planner thinks, so the search takes a long time. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
On Tue, Apr 6, 2010 at 6:30 PM, Joel Jacobson j...@gluefinance.com wrote: 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. Wow. That's very surprising to me... ...Robert
