Обсуждение: Re: LIMIT causes planner to do Index Scan using a less optimal index
Re: LIMIT causes planner to do Index Scan using a less optimal index
От
Sherry.CTR.Zhu@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 <joel@gluefinance.com> 04/06/2010 06:30 PM |
|
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.Zhu@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 |
|
Have you tried before?
Thanks much!
Xuefeng Zhu (Sherry)
Crown Consulting Inc. -- Oracle DBA
AIM Lab Data Team
(703) 925-3192
Robert Haas <robertmhaas@gmail.com> 04/06/2010 03:07 PM |
|
On Tue, Apr 6, 2010 at 3:05 PM, <Sherry.CTR.Zhu@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: jj@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
On Wed, Apr 7, 2010 at 1:20 PM, <Sherry.CTR.Zhu@faa.gov> wrote:
lies. postgresql allows you indices on multiple columns. What it does not have, is index on multiple tables.
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: LIMIT causes planner to do Index Scan using a less optimal index
От
Sherry.CTR.Zhu@faa.gov
Дата:
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 <gryzman@gmail.com> 04/07/2010 08:51 AM |
|
On Wed, Apr 7, 2010 at 1:20 PM, <Sherry.CTR.Zhu@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
2010/4/7 <Sherry.CTR.Zhu@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).
Don't listen to oracle folks, they obviously know not much about products others than oracle db(s).
--
GJ
Re: LIMIT causes planner to do Index Scan using a less optimal index
От
Sherry.CTR.Zhu@faa.gov
Дата:
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 <gryzman@gmail.com> Sent by: pgsql-performance-owner@postgresql.org 04/07/2010 09:12 AM |
|
2010/4/7 <Sherry.CTR.Zhu@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
On Wed, 7 Apr 2010, Sherry.CTR.Zhu@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 <gryzman@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.
Hi Xuefeng,
--
Best regards,
Joel Jacobson
Glue Finance
E: jj@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
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.Zhu@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 <joel@gluefinance.com>04/06/2010 06:30 PM
ToSherry CTR Zhu/AWA/CNTR/FAA@FAA, pgsql-performance@postgresql.org cc Robert Haas <robertmhaas@gmail.com> SubjectRe: [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.Zhu@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 Group04/06/2010 03:13 PM
ToRobert Haas <robertmhaas@gmail.com> ccJoel Jacobson <joel@gluefinance.com> SubjectRe: [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 <robertmhaas@gmail.com> 04/06/2010 03:07 PM
ToSherry CTR Zhu/AWA/CNTR/FAA@FAA ccJoel Jacobson <joel@gluefinance.com> SubjectRe: [PERFORM] LIMIT causes planner to do Index Scan using a less optimal index
On Tue, Apr 6, 2010 at 3:05 PM, <Sherry.CTR.Zhu@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: jj@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
--
Best regards,
Joel Jacobson
Glue Finance
E: jj@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