pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedLIMIT causes planner to do Index Scan using a less optimal index
11+ messages / 5 participants
[nested] [flat]
* LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-02 18:19 Joel Jacobson <joel@gluefinance.com>
0 siblings, 1 reply; 11+ messages in thread
From: Joel Jacobson @ 2010-04-02 18:19 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-06 18:33 Robert Haas <robertmhaas@gmail.com>
parent: Joel Jacobson <joel@gluefinance.com>
0 siblings, 0 replies; 11+ messages in thread
From: Robert Haas @ 2010-04-06 18:33 UTC (permalink / raw)
To: Joel Jacobson <joel@gluefinance.com>; +Cc: pgsql-performance
On Fri, Apr 2, 2010 at 2:19 PM, Joel Jacobson <joel@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-06 22:30 Joel Jacobson <joel@gluefinance.com>
0 siblings, 2 replies; 11+ messages in thread
From: Joel Jacobson @ 2010-04-06 22:30 UTC (permalink / raw)
To: Sherry.CTR.Zhu@faa.gov; pgsql-performance; +Cc: Robert Haas <robertmhaas@gmail.com>
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<Notes:///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 <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*<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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-07 00:22 Robert Haas <robertmhaas@gmail.com>
parent: Joel Jacobson <joel@gluefinance.com>
1 sibling, 0 replies; 11+ messages in thread
From: Robert Haas @ 2010-04-07 00:22 UTC (permalink / raw)
To: Joel Jacobson <joel@gluefinance.com>; +Cc: Sherry.CTR.Zhu@faa.gov; pgsql-performance
On Tue, Apr 6, 2010 at 6:30 PM, Joel Jacobson <joel@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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-07 12:20 Sherry.CTR.Zhu@faa.gov
parent: Joel Jacobson <joel@gluefinance.com>
1 sibling, 2 replies; 11+ messages in thread
From: Sherry.CTR.Zhu@faa.gov @ 2010-04-07 12:20 UTC (permalink / raw)
To: Joel Jacobson <joel@gluefinance.com>; +Cc: pgsql-performance@postgresql.org, Robert Haas <robertmhaas@gmail.com>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-07 12:51 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Sherry.CTR.Zhu@faa.gov
1 sibling, 1 reply; 11+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-04-07 12:51 UTC (permalink / raw)
To: Sherry.CTR.Zhu@faa.gov; +Cc: Joel Jacobson <joel@gluefinance.com>; pgsql-performance@postgresql.org, Robert Haas <robertmhaas@gmail.com>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-07 13:08 Sherry.CTR.Zhu@faa.gov
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Sherry.CTR.Zhu@faa.gov @ 2010-04-07 13:08 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: Joel Jacobson <joel@gluefinance.com>; pgsql-performance@postgresql.org, Robert Haas <robertmhaas@gmail.com>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-07 13:12 Grzegorz Jaśkiewicz <gryzman@gmail.com>
parent: Sherry.CTR.Zhu@faa.gov
0 siblings, 1 reply; 11+ messages in thread
From: Grzegorz Jaśkiewicz @ 2010-04-07 13:12 UTC (permalink / raw)
To: Sherry.CTR.Zhu@faa.gov; +Cc: Joel Jacobson <joel@gluefinance.com>; pgsql-performance@postgresql.org, Robert Haas <robertmhaas@gmail.com>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-07 13:22 Sherry.CTR.Zhu@faa.gov
parent: Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: Sherry.CTR.Zhu@faa.gov @ 2010-04-07 13:22 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: Joel Jacobson <joel@gluefinance.com>; pgsql-performance@postgresql.org, pgsql-performance-owner@postgresql.org, Robert Haas <robertmhaas@gmail.com>
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-07 13:36 Matthew Wakeling <matthew@flymine.org>
parent: Sherry.CTR.Zhu@faa.gov
0 siblings, 0 replies; 11+ messages in thread
From: Matthew Wakeling @ 2010-04-07 13:36 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: LIMIT causes planner to do Index Scan using a less optimal index
@ 2010-04-07 15:52 Joel Jacobson <joel@gluefinance.com>
parent: Sherry.CTR.Zhu@faa.gov
1 sibling, 0 replies; 11+ messages in thread
From: Joel Jacobson @ 2010-04-07 15:52 UTC (permalink / raw)
To: Sherry.CTR.Zhu@faa.gov; +Cc: pgsql-performance@postgresql.org, Robert Haas <robertmhaas@gmail.com>
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* <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* <robertmhaas@gmail.com>>
> cc
> Joel Jacobson <*joel@gluefinance.com* <joel@gluefinance.com>>
> Subject
> Re: [PERFORM] LIMIT causes planner to do Index Scan using a less
> optimal index*Link*<Notes:///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 <**robertmhaas@gmail.com* <robertmhaas@gmail.com>*>*
>
> 04/06/2010 03:07 PM
>
> To
> Sherry CTR Zhu/AWA/CNTR/FAA@FAA
> cc
> Joel Jacobson <*joel@gluefinance.com* <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*<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* <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
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2010-04-07 15:52 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-02 18:19 LIMIT causes planner to do Index Scan using a less optimal index Joel Jacobson <joel@gluefinance.com>
2010-04-06 18:33 ` Robert Haas <robertmhaas@gmail.com>
2010-04-06 22:30 Re: LIMIT causes planner to do Index Scan using a less optimal index Joel Jacobson <joel@gluefinance.com>
2010-04-07 00:22 ` Robert Haas <robertmhaas@gmail.com>
2010-04-07 12:20 ` Re: LIMIT causes planner to do Index Scan using a less optimal index Sherry.CTR.Zhu@faa.gov
2010-04-07 12:51 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-04-07 13:08 ` Re: LIMIT causes planner to do Index Scan using a less optimal index Sherry.CTR.Zhu@faa.gov
2010-04-07 13:12 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2010-04-07 13:22 ` Re: LIMIT causes planner to do Index Scan using a less optimal index Sherry.CTR.Zhu@faa.gov
2010-04-07 13:36 ` Matthew Wakeling <matthew@flymine.org>
2010-04-07 15:52 ` Joel Jacobson <joel@gluefinance.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox