Hi Hackers,

I (and at least four others) reported strange behaviour of PG 6.3(.1),
which under certain circumstances doesn't use indices like the versions
before.

So we still have to use 6.2.1 (now with the Massimo patches). For us
6.2.1 is three times faster than 6.3.

I have narrowed the problem down a bit, so please take a look:

We have two tables:

CREATE TABLE trans (spieler_nr int4, wpk_nr int4, state char, anzahl
int4, buyprice float8, buydate date, sellprice float8, selldate date,
mail char) archive = none;
CREATE TABLE kurse (wpk_nr int4, name text, curr char4, kurs float8,
datum date, art char, high float8, low float8, open float8, old float8)
archive = none;   

with three indices

CREATE  INDEX i_kurse_wpk_nr on kurse using btree ( wpk_nr int4_ops );
CREATE  INDEX i_trans_wpk_nr on trans using btree ( wpk_nr int4_ops );
CREATE  INDEX i_trans_spieler_nr on trans using btree ( spieler_nr
int4_ops );

If I do this select:

test=> explain SELECT * from Trans, Kurse where
Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
NOTICE:  QUERY PLAN:

Hash Join  (cost=408.60 size=1364 width=103)
  ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
  ->  Hash  (cost=0.00 size=0 width=0)
        ->  Index Scan on trans  (cost=3.41 size=29 width=38)

I get the seq scan, which slows the query down tremendously compared to
6.2. 

With the query:

test=> explain SELECT * from Trans, Kurse where
Kurse.wpk_nr=Trans.wpk_nr;
NOTICE:  QUERY PLAN:

Merge Join  (cost=7411.81 size=3343409 width=103)
  ->  Index Scan on kurse  (cost=337.90 size=4958 width=65)
  ->  Index Scan on trans  (cost=4563.60 size=71112 width=38)       

everything is fine.

For your convenience I have a dump of the database with some real world
data und the selects (and some vacuums of course) on our web server.

You can download it via HTTP

http://www.vocalweb.de/test_index.dump.gz

It's around 1 Mb.

Please take a look at this, cause this seems to be a major bug in
optimizer/analyzer code somewhere and we are not the only ones who see
this problem.

TIA

Ulrich

Fri, 27 Mar 1998 11:34:15 +0100
Ulrich Voss <voss@vocalweb.de>
Reminder: Indices are not used
> test=> explain SELECT * from Trans, Kurse where
> Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
> NOTICE:  QUERY PLAN:
> 
> Hash Join  (cost=408.60 size=1364 width=103)
>   ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
>   ->  Hash  (cost=0.00 size=0 width=0)
>         ->  Index Scan on trans  (cost=3.41 size=29 width=38)
> 
> I get the seq scan, which slows the query down tremendously compared to
> 6.2. 

This does help.  Vadim, can you check this?

-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Fri, 27 Mar 1998 14:26:59 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Reminder: Indices are not used
Bruce Momjian wrote:
> 
> > test=> explain SELECT * from Trans, Kurse where
> > Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
> > NOTICE:  QUERY PLAN:
> >
> > Hash Join  (cost=408.60 size=1364 width=103)
> >   ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
> >   ->  Hash  (cost=0.00 size=0 width=0)
> >         ->  Index Scan on trans  (cost=3.41 size=29 width=38)
> >
> > I get the seq scan, which slows the query down tremendously compared to
> > 6.2.
> 
> This does help.  Vadim, can you check this?

Ok.

Vadim

Mon, 30 Mar 1998 11:19:13 +0800
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Reminder: Indices are not used
Could you post EXPLAINs from 6.2 for the _same_ data/schema ?

As for 6.3 - I just added

CREATE INDEX i_trans on trans (spieler_nr, wpk_nr);

and see near the same performance for all possible plans (NestLoop,
MergeJoin & HashJoin) - you are able to restrict possible plans
using -fX backend' option... NestLoop is slowest (I used -fh -fm to
get it).

My recommendation is to don't create 1-key indices - trans(spieler_nr) &
trans(wpk_nr), - but create 2-key indices - trans (spieler_nr, wpk_nr) &
trans (wpk_nr, spieler_nr).

Nevertheless, I'm interested in 6.2(.1 ?) EXPLAIN..

Vadim

Ulrich Voss wrote:
> 
> Hi Hackers,
> 
> I (and at least four others) reported strange behaviour of PG 6.3(.1),
> which under certain circumstances doesn't use indices like the versions
> before.
> 
> So we still have to use 6.2.1 (now with the Massimo patches). For us
> 6.2.1 is three times faster than 6.3.
> 
> I have narrowed the problem down a bit, so please take a look:
> 
> We have two tables:
> 
> CREATE TABLE trans (spieler_nr int4, wpk_nr int4, state char, anzahl
> int4, buyprice float8, buydate date, sellprice float8, selldate date,
> mail char) archive = none;
> CREATE TABLE kurse (wpk_nr int4, name text, curr char4, kurs float8,
> datum date, art char, high float8, low float8, open float8, old float8)
> archive = none;
> 
> with three indices
> 
> CREATE  INDEX i_kurse_wpk_nr on kurse using btree ( wpk_nr int4_ops );
> CREATE  INDEX i_trans_wpk_nr on trans using btree ( wpk_nr int4_ops );
> CREATE  INDEX i_trans_spieler_nr on trans using btree ( spieler_nr
> int4_ops );
> 
> If I do this select:
> 
> test=> explain SELECT * from Trans, Kurse where
> Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
> NOTICE:  QUERY PLAN:
> 
> Hash Join  (cost=408.60 size=1364 width=103)
>   ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
>   ->  Hash  (cost=0.00 size=0 width=0)
>         ->  Index Scan on trans  (cost=3.41 size=29 width=38)
> 
> I get the seq scan, which slows the query down tremendously compared to
> 6.2.
> 
> With the query:
> 
> test=> explain SELECT * from Trans, Kurse where
> Kurse.wpk_nr=Trans.wpk_nr;
> NOTICE:  QUERY PLAN:
> 
> Merge Join  (cost=7411.81 size=3343409 width=103)
>   ->  Index Scan on kurse  (cost=337.90 size=4958 width=65)
>   ->  Index Scan on trans  (cost=4563.60 size=71112 width=38)
> 
> everything is fine.
> 
> For your convenience I have a dump of the database with some real world
> data und the selects (and some vacuums of course) on our web server.
> 
> You can download it via HTTP
> 
> http://www.vocalweb.de/test_index.dump.gz
> 
> It's around 1 Mb.
> 
> Please take a look at this, cause this seems to be a major bug in
> optimizer/analyzer code somewhere and we are not the only ones who see
> this problem.
> 
> TIA
> 
> Ulrich

Tue, 31 Mar 1998 16:20:55 +0800
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Reminder: Indices are not used
Hi,

boersenspiel=> explain SELECT * from Trans, Kurse where
Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3; NOTICE: QUERY PLAN:

Nested Loop  (cost=6.15 size=2 width=103)
  ->   Index Scan on trans  (cost=2.05 size=2 width=38)
  ->   Index Scan on kurse  (cost=2.05 size=14307 width=65)

EXPLAIN

(Funny, the query which uses indices the right way in 6.3 is wrong in 
6.2.1, but who cares if multi-key-indices get used ...

boersenspiel=> explain SELECT * from Trans, Kurse where
Kurse.wpk_nr=Trans.wpk_n r; NOTICE: QUERY PLAN:

Hash Join  (cost=18425.21 size=175546 width=103)
  ->   Seq Scan on trans  (cost=8134.02 size=175546 width=38)
  ->   Hash  (cost=0.00 size=0 width=0)
    ->     Seq Scan on kurse  (cost=712.13 size=14307 width=65)

EXPLAIN
)

> Could you post EXPLAINs from 6.2 for the _same_ data/schema ?
> 
> As for 6.3 - I just added
> 
> CREATE INDEX i_trans on trans (spieler_nr, wpk_nr);
> 
> and see near the same performance for all possible plans (NestLoop,
> MergeJoin & HashJoin) - you are able to restrict possible plans
> using -fX backend' option... NestLoop is slowest (I used -fh -fm to
> get it).
> 
> My recommendation is to don't create 1-key indices - trans(spieler_nr) &
> trans(wpk_nr), - but create 2-key indices - trans (spieler_nr, wpk_nr) &
> trans (wpk_nr, spieler_nr).
> 
> Nevertheless, I'm interested in 6.2(.1 ?) EXPLAIN..
> 
> Vadim
> 
> Ulrich Voss wrote:
> > 
> > Hi Hackers,
> > 
> > I (and at least four others) reported strange behaviour of PG 6.3(.1),
> > which under certain circumstances doesn't use indices like the versions
> > before.
> > 
> > So we still have to use 6.2.1 (now with the Massimo patches). For us
> > 6.2.1 is three times faster than 6.3.
> > 
> > I have narrowed the problem down a bit, so please take a look:
> > 
> > We have two tables:
> > 
> > CREATE TABLE trans (spieler_nr int4, wpk_nr int4, state char, anzahl
> > int4, buyprice float8, buydate date, sellprice float8, selldate date,
> > mail char) archive = none;
> > CREATE TABLE kurse (wpk_nr int4, name text, curr char4, kurs float8,
> > datum date, art char, high float8, low float8, open float8, old float8)
> > archive = none;
> > 
> > with three indices
> > 
> > CREATE  INDEX i_kurse_wpk_nr on kurse using btree ( wpk_nr int4_ops );
> > CREATE  INDEX i_trans_wpk_nr on trans using btree ( wpk_nr int4_ops );
> > CREATE  INDEX i_trans_spieler_nr on trans using btree ( spieler_nr
> > int4_ops );
> > 
> > If I do this select:
> > 
> > test=> explain SELECT * from Trans, Kurse where
> > Kurse.wpk_nr=Trans.wpk_nr and Trans.spieler_nr=3;
> > NOTICE:  QUERY PLAN:
> > 
> > Hash Join  (cost=408.60 size=1364 width=103)
> >   ->  Seq Scan on kurse  (cost=238.61 size=4958 width=65)
> >   ->  Hash  (cost=0.00 size=0 width=0)
> >         ->  Index Scan on trans  (cost=3.41 size=29 width=38)
> > 
> > I get the seq scan, which slows the query down tremendously compared to
> > 6.2.
> > 
> > With the query:
> > 
> > test=> explain SELECT * from Trans, Kurse where
> > Kurse.wpk_nr=Trans.wpk_nr;
> > NOTICE:  QUERY PLAN:
> > 
> > Merge Join  (cost=7411.81 size=3343409 width=103)
> >   ->  Index Scan on kurse  (cost=337.90 size=4958 width=65)
> >   ->  Index Scan on trans  (cost=4563.60 size=71112 width=38)
> > 
> > everything is fine.
> > [...] 

Ciao

Das Boersenspielteam.

---------------------------------------------------------------------------
                          http://www.boersenspiel.de
              	         Das Boersenspiel im Internet
             *Realitaetsnah*  *Kostenlos*  *Ueber 6000 Spieler*
---------------------------------------------------------------------------

Tue, 31 Mar 1998 12:30:35 +0000
"Boersenspielteam" <boersenspiel@vocalweb.de>
Re: [HACKERS] Reminder: Indices are not used
Thanks for help!

This is patch for src/backend/optimizer/path/prune.c.
Access pathes of pruned joinrels were not merged and better
pathes were lost, sometimes...

Vadim
Thu, 02 Apr 1998 15:39:50 +0800
"Vadim B. Mikheev" <vadim@sable.krasnoyarsk.su>
Re: [HACKERS] Reminder: Indices are not used
> Thanks for help!
> 
> This is patch for src/backend/optimizer/path/prune.c.
> Access pathes of pruned joinrels were not merged and better
> pathes were lost, sometimes...

Gee, I am sorry Vadim.  This is one of the optimizer functions I tried
to clean up, but obviously broke it.

> 
> *** prune.c.orig	Thu Apr  2 14:56:54 1998
> --- prune.c	Thu Apr  2 15:16:17 1998
> ***************
> *** 61,99 ****


-- 
Bruce Momjian                          |  830 Blythe Avenue
maillist@candle.pha.pa.us              |  Drexel Hill, Pennsylvania 19026
  +  If your life is a hard drive,     |  (610) 353-9879(w)
  +  Christ can be your backup.        |  (610) 853-3000(h)

Thu, 2 Apr 1998 10:23:05 -0500 (EST)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] Reminder: Indices are not used
Thanks Vadim!

This patch does help.

The simple join still uses two sequential scans though.

Is your tip regarding single-key vs. multi-key-indices still valid?
I don't see any performance difference between these two.

BTW: Has the spinlock code, that was discussed some days ago, made it 
into 6.3.1.? 

Ciao

Ulrich

> Thanks for help!
> 
> This is patch for src/backend/optimizer/path/prune.c.
> Access pathes of pruned joinrels were not merged and better
> pathes were lost, sometimes...
> 
> Vadim
> 

Ciao

Das Boersenspielteam.

---------------------------------------------------------------------------
                          http://www.boersenspiel.de
              	         Das Boersenspiel im Internet
             *Realitaetsnah*  *Kostenlos*  *Ueber 6000 Spieler*
---------------------------------------------------------------------------

Fri, 3 Apr 1998 19:01:23 +0000
"Boersenspielteam" <boersenspiel@vocalweb.de>
Re: [HACKERS] Reminder: Indices are not used
Das Boersenspielteam:
> 
> BTW: Has the spinlock code, that was discussed some days ago, made it 
> into 6.3.1.? 

I have not sent in the patch yet, so it is not in 6.3.1. I have a couple of
other tasks related to my real life that have cut into my time lately. I
hope to free up and finish the spinlock patch next week.

Thanks
-dg 

David Gould            dg@illustra.com           510.628.3783 or 510.305.9468 
Informix Software  (No, really)         300 Lakeside Drive  Oakland, CA 94612
 - Linux. Not because it is free. Because it is better.

Fri, 3 Apr 1998 11:55:58 -0800 (PST)
dg@illustra.com (David Gould)
Re: [HACKERS] Reminder: Indices are not used