Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id D79E7634BEE for ; Wed, 7 Apr 2010 12:53:00 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 74224-01 for ; Wed, 7 Apr 2010 15:52:47 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail-bw0-f216.google.com (mail-bw0-f216.google.com [209.85.218.216]) by mail.postgresql.org (Postfix) with ESMTP id 0FE82634DAF for ; Wed, 7 Apr 2010 12:52:32 -0300 (ADT) Received: by bwz8 with SMTP id 8so1019067bwz.3 for ; Wed, 07 Apr 2010 08:52:31 -0700 (PDT) MIME-Version: 1.0 Received: by 10.204.34.20 with HTTP; Wed, 7 Apr 2010 08:52:30 -0700 (PDT) In-Reply-To: References: Date: Wed, 7 Apr 2010 17:52:30 +0200 Received: by 10.204.5.87 with SMTP id 23mr9634398bku.206.1270655550664; Wed, 07 Apr 2010 08:52:30 -0700 (PDT) Message-ID: Subject: Re: LIMIT causes planner to do Index Scan using a less optimal index From: Joel Jacobson To: Sherry.CTR.Zhu@faa.gov Cc: pgsql-performance@postgresql.org, Robert Haas Content-Type: multipart/alternative; boundary=00151758ac54bab3d80483a78cef X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201004/100 X-Sequence-Number: 38350 --00151758ac54bab3d80483a78cef Content-Type: text/plain; charset=ISO-8859-1 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 > > 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 * > > 04/06/2010 06:30 PM > To > Sherry CTR Zhu/AWA/CNTR/FAA@FAA, pgsql-performance@postgresql.org > cc > Robert Haas > 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 index*Link* > > > > > > 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 --00151758ac54bab3d80483a78cef Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 "LIMI= T 1".

=


2010/4/7 &l= t;Sherry.CTR.Zhu@faa.gov><= /span>

Guys,

=A0 Thanks for trying and opening your mind. =A0
=A0 If you want to know how Oracle addressed this issue, here it is: =A0index on two columns. =A0I 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@F= AA, p= gsql-performance@postgresql.org
cc
Robert Haas= <robertmhaas= @gmail.com>
Subject
Re: [PERFORM] LIMIT causes pl= anner to do Index Scan using a less =A0 =A0 =A0 =A0 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 t= o 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 =3D 108) AND (Currency =3D 'SEK')) ORDER BY Trans= actionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=A0Limit =A0(cost=3D0.00..116.02 rows=3D1 width=3D4) (= actual time=3D1384.401..1384.402 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index Scan using transactions_pkey on transactions =A0(cost=3D0.00..1260254.03 rows=3D10862 width=3D4) (actual time=3D1384.399..1384.399 rows=3D1 loops=3D1)
=A0=A0 =A0 =A0 =A0 Filter: ((accountid =3D 108) AND (currency =3D 'SEK'::bpchar))
=A0Total runtime: 1384.431 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =3D 'SEK') AND (AccountID =3D 108)) ORDER BY Trans= actionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=A0Limit =A0(cost=3D0.00..116.02 rows=3D1 width=3D4) (= actual time=3D1710.166..1710.167 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index Scan using transactions_pkey on transactions =A0(cost=3D0.00..1260254.03 rows=3D10862 width=3D4) (actual time=3D1710.164..1710.164 rows=3D1 loops=3D1)
=A0=A0 =A0 =A0 =A0 Filter: ((currency =3D 'SEK'::bpchar) AND (accountid =3D 108))
=A0Total runtime: 1710.200 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =3D 108) AND (Currency =3D 'SEK')) ORDER BY Trans= actionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=A0Limit =A0(cost=3D0.00..116.02 rows=3D1 width=3D4) (= actual time=3D1366.526..1366.527 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index Scan using transactions_pkey on transactions =A0(cost=3D0.00..1260254.03 rows=3D10862 width=3D4) (actual time=3D1366.525..1366.525 rows=3D1 loops=3D1)
=A0=A0 =A0 =A0 =A0 Filter: ((accountid =3D 108) AND (currency =3D 'SEK'::bpchar))
=A0Total runtime: 1366.552 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =3D 'SEK') AND (AccountID =3D 108)) ORDER BY Trans= actionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=A0Limit =A0(cost=3D0.00..116.02 rows=3D1 width=3D4) (= actual time=3D1685.395..1685.396 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index Scan using transactions_pkey on transactions =A0(cost=3D0.00..1260254.03 rows=3D10862 width=3D4) (actual time=3D1685.394..1685.394 rows=3D1 loops=3D1)
=A0=A0 =A0 =A0 =A0 Filter: ((currency =3D 'SEK'::bpchar) AND (accountid =3D 108))
=A0Total runtime: 1685.423 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =3D 108) AND (Currency =3D 'SEK')) ORDER BY Trans= actionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=A0Limit =A0(cost=3D0.00..116.02 rows=3D1 width=3D4) (= actual time=3D1403.904..1403.905 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index Scan using transactions_pkey on transactions =A0(cost=3D0.00..1260254.03 rows=3D10862 width=3D4) (actual time=3D1403.903..1403.903 rows=3D1 loops=3D1)
=A0=A0 =A0 =A0 =A0 Filter: ((accountid =3D 108) AND (currency =3D 'SEK'::bpchar))
=A0Total runtime: 1403.931 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =3D 'SEK') AND (AccountID =3D 108)) ORDER BY Trans= actionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=A0Limit =A0(cost=3D0.00..116.02 rows=3D1 width=3D4) (= actual time=3D1689.014..1689.014 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index Scan using transactions_pkey on transactions =A0(cost=3D0.00..1260254.03 rows=3D10862 width=3D4) (actual time=3D1689.012..1689.012 rows=3D1 loops=3D1)
=A0=A0 =A0 =A0 =A0 Filter: ((currency =3D 'SEK'::bpchar) AND (accountid =3D 108))
=A0Total runtime: 1689.041 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((AccountID =3D 108) AND (Currency =3D 'SEK')) ORDER BY Trans= actionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=A0Limit =A0(cost=3D0.00..116.02 rows=3D1 width=3D4) (= actual time=3D1378.322..1378.323 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index Scan using transactions_pkey on transactions =A0(cost=3D0.00..1260254.03 rows=3D10862 width=3D4) (actual time=3D1378.320..1378.320 rows=3D1 loops=3D1)
=A0=A0 =A0 =A0 =A0 Filter: ((accountid =3D 108) AND (currency =3D 'SEK'::bpchar))
=A0Total runtime: 1378.349 ms
(4 rows)

EXPLAIN ANALYZE SELECT TransactionID FROM Transactions WHERE ((Currency =3D 'SEK') AND (AccountID =3D 108)) ORDER BY Trans= actionID LIMIT 1;
=A0=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0QUERY PLAN =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------
=A0Limit =A0(cost=3D0.00..116.02 rows=3D1 width=3D4) (= actual time=3D1696.830..1696.831 rows=3D1 loops=3D1)
=A0=A0 -> =A0Index Scan using transactions_pkey on transactions =A0(cost=3D0.00..1260254.03 rows=3D10862 width=3D4) (actual time=3D1696.828..1696.828 rows=3D1 loops=3D1)
=A0=A0 =A0 =A0 =A0 Filter: ((currency =3D 'SEK'::bpchar) AND (accountid =3D 108))
=A0Total 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/AW= A/CNTR/FAA
AJR-32, Aeronautical Information M= gmt Group

04/06/2010 03:13 PM


To
Robert Haas <= ;robertmhaas@gmail.com>
cc
Joel Jacobson <joel@gluefinance.com>
Subject
Re: [PERFORM] LIMIT causes pl= anner to do Index Scan using a less =A0 =A0 =A0 =A0 optimal indexLink<= /u>






Have you tried before?


Thanks much!

Xuefeng Zhu (Sherry)
Crown Consulting Inc. -- Oracle DBA
AIM Lab Data Team
(703) 925-3192



Robert Haas <<= /b>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 pl= anner to do Index Scan using a less =A0 =A0 =A0 =A0 optimal index







On Tue, Apr 6, 2010 at 3:05 PM, <
Sherry.CTR.Z= hu@faa.gov> wrote:

Just curious,


Switch the where condition to try to make difference.


how about change

((accountid =3D 108) AND (currency =3D 'SEK'::bpchar))
<= font size=3D"3">
to
(
(currency =3D 'SEK'::bpchar) AND (accountid =3D 108) ).


In earlier version of Oracle, this was common knowledge that optimizer took the last condition index to use. =A0


Ignore me if you think this is no sence. =A0I didn't have a time to read your guys' all emails. =A0


PostgreSQL doesn't behave that way - it guesses which order will be che= aper.

...Robert




--
Best regards,

Joel Jacobson
Glue Finance

E:
jj@gluefinance.com
T: +46 70 360 38 01

Postal address:
Glue Finance AB
Box =A0549
114 11 =A0Stockholm
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

Pos= tal address:
Glue Finance AB
Box =A0549
114 11 =A0Stockholm
Sweden

Visit= ing address:
Glue Finance AB
Birger Jarlsgatan 14
114 34 Stockholm=
Sweden
--00151758ac54bab3d80483a78cef--