Обсуждение: 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

To
Sherry CTR Zhu/AWA/CNTR/FAA@FAA, pgsql-performance@postgresql.org
cc
Robert Haas <robertmhaas@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.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


To
Robert Haas <robertmhaas@gmail.com>
cc
Joel Jacobson <joel@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 <robertmhaas@gmail.com>

04/06/2010 03:07 PM


To
Sherry CTR Zhu/AWA/CNTR/FAA@FAA
cc
Joel Jacobson <joel@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.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

Re: LIMIT causes planner to do Index Scan using a less optimal index

От
Grzegorz Jaśkiewicz
Дата:


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

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

To
Sherry CTR Zhu/AWA/CNTR/FAA@FAA
cc
Joel Jacobson <joel@gluefinance.com>, pgsql-performance@postgresql.org, Robert Haas <robertmhaas@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.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

Re: LIMIT causes planner to do Index Scan using a less optimal index

От
Grzegorz Jaśkiewicz
Дата:


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

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

To
Sherry CTR Zhu/AWA/CNTR/FAA@FAA
cc
Joel Jacobson <joel@gluefinance.com>, pgsql-performance@postgresql.org, Robert Haas <robertmhaas@gmail.com>
Subject
Re: [PERFORM] LIMIT causes planner to do Index Scan using a less         optimal index







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

Re: LIMIT causes planner to do Index Scan using a less optimal index

От
Matthew Wakeling
Дата:
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.

Re: LIMIT causes planner to do Index Scan using a less optimal index

От
Joel Jacobson
Дата:
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.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

To
Sherry CTR Zhu/AWA/CNTR/FAA@FAA, pgsql-performance@postgresql.org
cc
Robert Haas <robertmhaas@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.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


To
Robert Haas <robertmhaas@gmail.com>
cc
Joel Jacobson <joel@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 <robertmhaas@gmail.com>

04/06/2010 03:07 PM


To
Sherry CTR Zhu/AWA/CNTR/FAA@FAA
cc
Joel Jacobson <joel@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.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