Hi,


I've got performance problem and while I'dont ready to describe it
I'd like to ask about strange explain:

tour=# explain analyze  select * from tours  where
             ( operator_id in (2,3,4,5,7) and type_id = 2 )  or
             ( operator_id = 8 and type_id=4 );

NOTICE:  QUERY PLAN:

Index Scan using type_idx, type_idx, type_idx, type_idx, type_idx, type_idx on tours  (cost=0.00..12.25 rows=1 width=1091) (actual time=0.26..0.26 rows=0 loops=1)
Total runtime: 0.45 msec

EXPLAIN

What does many 'type_idx' means ?

Theare are 2 indices - operator_idx and type_idx.


	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Mon, 13 May 2002 16:42:10 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
strange explain
Oleg Bartunov <oleg@sai.msu.su> writes:
> tour=# explain analyze  select * from tours  where
>              ( operator_id in (2,3,4,5,7) and type_id = 2 )  or
>              ( operator_id = 8 and type_id=4 );

> Index Scan using type_idx, type_idx, type_idx, type_idx, type_idx, type_idx on tours  (cost=0.00..12.25 rows=1 width=1091) (actual time=0.26..0.26 rows=0 loops=1)

> What does many 'type_idx' means ?

Multiple indexscans.

It looks to me like your WHERE clause is being flattened into

             ( operator_id = 2 and type_id=2 ) or
             ( operator_id = 3 and type_id=2 ) or
             ( operator_id = 4 and type_id=2 ) or
             ( operator_id = 5 and type_id=2 ) or
             ( operator_id = 7 and type_id=2 ) or
             ( operator_id = 8 and type_id=4 )

and then it has a choice of repeated indexscans on operator_id or
type_id.  Depending on the selectivity stats it might pick either.
You might find that a 2-column index on both would be a win.

			regards, tom lane

Mon, 13 May 2002 10:04:50 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange explain 
It appears it scanes the type_idx once per opereator.  IN gets broken
down into ORs

Is this what the TODO entry 'Make IN / NOT IN have similar performance
as EXISTS' means?
--
Rod
----- Original Message -----
From: "Oleg Bartunov" <oleg@sai.msu.su>
To: "Pgsql Hackers" <pgsql-hackers@postgresql.org>; "Tom Lane"
<tgl@sss.pgh.pa.us>
Sent: Monday, May 13, 2002 9:42 AM
Subject: [HACKERS] strange explain


> Hi,
>
>
> I've got performance problem and while I'dont ready to describe it
> I'd like to ask about strange explain:
>
> tour=# explain analyze  select * from tours  where
>              ( operator_id in (2,3,4,5,7) and type_id = 2 )  or
>              ( operator_id = 8 and type_id=4 );
>
> NOTICE:  QUERY PLAN:
>
> Index Scan using type_idx, type_idx, type_idx, type_idx, type_idx,
type_idx on tours  (cost=0.00..12.25 rows=1 width=1091) (actual
time=0.26..0.26 rows=0 loops=1)
> Total runtime: 0.45 msec
>
> EXPLAIN
>
> What does many 'type_idx' means ?
>
> Theare are 2 indices - operator_idx and type_idx.
>
>
> Regards,
> Oleg
> _____________________________________________________________
> Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
> Sternberg Astronomical Institute, Moscow University (Russia)
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(095)939-16-83, +007(095)939-23-83
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
>


Mon, 13 May 2002 11:17:27 -0400
"Rod Taylor" <rbt@zort.ca>
Re: strange explain
"Rod Taylor" <rbt@zort.ca> writes:
> Is this what the TODO entry 'Make IN / NOT IN have similar performance
> as EXISTS' means?

No.  The TODO item is talking about IN with a sub-SELECT, which is not
optimized at all at the moment.  IN with a list of scalar values is
converted to ((x = value1) OR (x = value2) OR ...), which we can do
something with.

			regards, tom lane

Mon, 13 May 2002 11:24:59 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange explain 
Thanks Tom,


On Mon, 13 May 2002, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
> > tour=# explain analyze  select * from tours  where
> >              ( operator_id in (2,3,4,5,7) and type_id = 2 )  or
> >              ( operator_id = 8 and type_id=4 );
>
> > Index Scan using type_idx, type_idx, type_idx, type_idx, type_idx, type_idx on tours  (cost=0.00..12.25 rows=1 width=1091) (actual time=0.26..0.26 rows=0 loops=1)
>
> > What does many 'type_idx' means ?
>
> Multiple indexscans.
>
> It looks to me like your WHERE clause is being flattened into
>
>              ( operator_id = 2 and type_id=2 ) or
>              ( operator_id = 3 and type_id=2 ) or
>              ( operator_id = 4 and type_id=2 ) or
>              ( operator_id = 5 and type_id=2 ) or
>              ( operator_id = 7 and type_id=2 ) or
>              ( operator_id = 8 and type_id=4 )
>

this is what I assume.

> and then it has a choice of repeated indexscans on operator_id or
> type_id.  Depending on the selectivity stats it might pick either.
> You might find that a 2-column index on both would be a win.
>

Yes, we've went exactly this way.

I'm very exited how planner could be smart. When I played with the query
and specify different values of type_id I notice it's chose plans depends
on is value exists or not.


> 			regards, tom lane
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Mon, 13 May 2002 19:08:36 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: strange explain 
Tom,

one more question.

What's the difference for planner between 2 queries ?
For the first query I have plain index scan, but multiple
index scan for second.

tour=# explain analyze  select * from tours  where
              ( operator_id in (2,3,4,5,7) and type_id = 2 );
NOTICE:  QUERY PLAN:

Index Scan using type_idx on tours  (cost=0.00..2.03 rows=1 width=1091) (actual time=0.03..0.03 rows=0 loops=1)
Total runtime: 0.16 msec

EXPLAIN
tour=# explain analyze  select * from tours  where
           ( operator_id in (2,3,4,5,7) and type_id = 4 ) or
           ( operator_id = 8 and type_id = 3);
NOTICE:  QUERY PLAN:

Index Scan using type_idx, type_idx, type_idx, type_idx, type_idx, type_idx on tours  (cost=0.00..12.25 rows=1 width=1091) (actual time=0.27..0.27 rows=0 loops=1)
Total runtime: 0.44 msec

EXPLAIN




On Mon, 13 May 2002, Tom Lane wrote:

> Oleg Bartunov <oleg@sai.msu.su> writes:
> > tour=# explain analyze  select * from tours  where
> >              ( operator_id in (2,3,4,5,7) and type_id = 2 )  or
> >              ( operator_id = 8 and type_id=4 );
>
> > Index Scan using type_idx, type_idx, type_idx, type_idx, type_idx, type_idx on tours  (cost=0.00..12.25 rows=1 width=1091) (actual time=0.26..0.26 rows=0 loops=1)
>
> > What does many 'type_idx' means ?
>
> Multiple indexscans.
>
> It looks to me like your WHERE clause is being flattened into
>
>              ( operator_id = 2 and type_id=2 ) or
>              ( operator_id = 3 and type_id=2 ) or
>              ( operator_id = 4 and type_id=2 ) or
>              ( operator_id = 5 and type_id=2 ) or
>              ( operator_id = 7 and type_id=2 ) or
>              ( operator_id = 8 and type_id=4 )
>
> and then it has a choice of repeated indexscans on operator_id or
> type_id.  Depending on the selectivity stats it might pick either.
> You might find that a 2-column index on both would be a win.
>
> 			regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

	Regards,
		Oleg
_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83


Mon, 13 May 2002 19:30:17 +0300 (GMT)
Oleg Bartunov <oleg@sai.msu.su>
Re: strange explain 
Oleg Bartunov <oleg@sai.msu.su> writes:
> What's the difference for planner between 2 queries ?

> tour=# explain analyze  select * from tours  where
>               ( operator_id in (2,3,4,5,7) and type_id = 2 );

> tour=# explain analyze  select * from tours  where
>            ( operator_id in (2,3,4,5,7) and type_id = 4 ) or
>            ( operator_id = 8 and type_id = 3);

The first one's already in normal form and doesn't need any more
flattening.  I believe the system will consider a multiple indexscan
on operator_idx for it, but probably the cost estimator is concluding
that that's a loser compared to one indexscan using type_id = 2.
Without any info on the selectivity of these conditions it's hard to say
whether that's a correct choice or not.

			regards, tom lane

Mon, 13 May 2002 13:10:23 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: strange explain 