pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
New Optimizer Behaviour In 7.0b1
6+ messages / 2 participants
[nested] [flat]

* New Optimizer Behaviour In 7.0b1
@ 2000-02-25 03:03 Mark Kirkwood <markir@ihug.co.nz>
  0 siblings, 0 replies; 6+ messages in thread

From: Mark Kirkwood @ 2000-02-25 03:03 UTC (permalink / raw)
  To: pgsql-sql

I have been playing a bit with this new release. There are lots of new
possible plans, which is really great.

I have been using the query shown below to study optimizer changes.

select
       d0.d0f1,
       count(f.f1)
from dim0 d0,
     fact1 f
where d0.d0key = f.d0key
and   d0.d0f1 between '1999-11-01' and '1999-12-01'
group by d0.d0f1

Table setup is :

 Table "fact1"  size  300000 rows
 Attribute |  Type   | Modifier
-----------+---------+----------
 d0key     | integer |
 d1key     | integer |
 f1           | integer |
Index: fact1_q1 on d0key

 Table "dim0"  size  900 rows
 Attribute |    Type     | Modifier
-----------+-------------+----------
 d0key    | integer        |
 d0f1      | timestamp   |
 d0f2      | varchar(20) |
 d0f3      | varchar(20) |
Indices: dim0_pk on d0key,
              dim0_q1 on d0f1

Explain is :
Aggregate  (cost=12205.78..12372.44 rows=3333 width=20)
  ->  Group  (cost=12205.78..12289.11 rows=33333 width=20)
        ->  Sort  (cost=12205.78..12205.78 rows=33333 width=20)
              ->  Hash Join  (cost=21.75..9371.33 rows=33333 width=20)
                    ->  Seq Scan on fact1 f  (cost=0.00..4765.00
rows=300000 width=8)
                    ->  Hash  (cost=21.50..21.50 rows=100 width=12)
                          ->  Seq Scan on dim0 d0  (cost=0.00..21.50
rows=100 width=12)

Initially this ran fairly slowly : 8-10s , the query scans about 9000
out the 300000 in the big table(fact1).
A bit of tweeking with the set variables : ( these are new -see
src/backend/commands/variable.c )
set cpu_tuple_cost = '0.6';
set enable_hashjoin = 'off';
set enable_mergejoin = 'off';

gave a new plan :
Aggregate  (cost=0.00..18476945.83 rows=3333 width=20)
  ->  Group  (cost=0.00..18476862.50 rows=33333 width=20)
        ->  Nested Loop  (cost=0.00..18476779.16 rows=33333 width=20)
              ->  Index Scan using dim0_q1 on dim0 d0  (cost=0.00..81.98
rows=100 width=12)
              ->  Index Scan using fact1_q1 on fact1 f
(cost=0.00..4016.97 rows=1500 width=8)

which is devestatiingly fast... about 1 s. Note that the table order is
reversed and that the index on the big
table ( fact1) is used.

However it seems a bit on the brutal side to have to coerce the
optimizer this way ( after all hash joins are
generally good), is there any way to get a reasonably sensible use of
indexes without such desperate
measures ?

P.s : I realize that this is beta 1..... I am impressed, I have had no
problems relinking php4 and  subsequently apache for use with this
release - seems like a very good quality beta 1. well done guys!

Mark
(markir@ihug.co.nz,
 mark.kirkwood@hnz.co.nz )




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* New Optimizer Behaviour In 7.0b1
@ 2000-02-26 06:57 Mark Kirkwood <markir@ihug.co.nz>
  2000-02-26 08:04 ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Mark Kirkwood @ 2000-02-26 06:57 UTC (permalink / raw)
  To: pgsql-sql

I have been playing a bit with this new release. There are lots of new
possible plans, which is really great.

I have been using the query shown below to study optimizer changes.

select
       d0.d0f1,
       count(f.f1)
from dim0 d0,
     fact1 f
where d0.d0key = f.d0key
and   d0.d0f1 between '1999-11-01' and '1999-12-01'
group by d0.d0f1

Table setup is :

 Table "fact1"  size  300000 rows
 Attribute |  Type   | Modifier
-----------+---------+----------
 d0key     | integer |
 d1key     | integer |
 f1           | integer |
Index: fact1_q1 on d0key

 Table "dim0"  size  900 rows
 Attribute |    Type     | Modifier
-----------+-------------+----------
 d0key    | integer        |
 d0f1      | timestamp   |
 d0f2      | varchar(20) |
 d0f3      | varchar(20) |
Indices: dim0_pk on d0key,
              dim0_q1 on d0f1

Explain is :
Aggregate  (cost=12205.78..12372.44 rows=3333 width=20)
  ->  Group  (cost=12205.78..12289.11 rows=33333 width=20)
        ->  Sort  (cost=12205.78..12205.78 rows=33333 width=20)
              ->  Hash Join  (cost=21.75..9371.33 rows=33333 width=20)
                    ->  Seq Scan on fact1 f  (cost=0.00..4765.00
rows=300000 width=8)
                    ->  Hash  (cost=21.50..21.50 rows=100 width=12)
                          ->  Seq Scan on dim0 d0  (cost=0.00..21.50
rows=100 width=12)

Initially this ran fairly slowly : 8-10s , the query scans about 9000
out the 300000 in the big table(fact1).
A bit of tweeking with the set variables : ( these are new -see
src/backend/commands/variable.c )
set cpu_tuple_cost = '0.6';
set enable_hashjoin = 'off';
set enable_mergejoin = 'off';

gave a new plan :
Aggregate  (cost=0.00..18476945.83 rows=3333 width=20)
  ->  Group  (cost=0.00..18476862.50 rows=33333 width=20)
        ->  Nested Loop  (cost=0.00..18476779.16 rows=33333 width=20)
              ->  Index Scan using dim0_q1 on dim0 d0  (cost=0.00..81.98
rows=100 width=12)
              ->  Index Scan using fact1_q1 on fact1 f
(cost=0.00..4016.97 rows=1500 width=8)

which is devestatiingly fast... about 1 s. Note that the table order is
reversed and that the index on the big
table ( fact1) is used.

However it seems a bit on the brutal side to have to coerce the
optimizer this way ( after all hash joins are
generally good), is there any way to get a reasonably sensible use of
indexes without such desperate
measures ?

P.s : I realize that this is beta 1..... I am impressed, I have had no
problems relinking php4 and  subsequently apache for use with this
release - seems like a very good quality beta 1. well done guys!

Mark
(markir@ihug.co.nz,
 mark.kirkwood@hnz.co.nz )

P.p.s : hopefully this is not going to appear twice on this list, my
first send bounced.



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] New Optimizer Behaviour In 7.0b1
  2000-02-26 06:57 New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
@ 2000-02-26 08:04 ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-02-26 20:23   ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2000-02-26 08:04 UTC (permalink / raw)
  To: Mark Kirkwood <markir@ihug.co.nz>; +Cc: pgsql-sql

Mark Kirkwood <markir@ihug.co.nz> writes:
> I have been using the query shown below to study optimizer changes.

> [ results snipped ]

> However it seems a bit on the brutal side to have to coerce the
> optimizer this way ( after all hash joins are generally good), is
> there any way to get a reasonably sensible use of indexes without such
> desperate measures ?

Obviously we'd like to get the optimizer to do the right thing without
being beaten over the head ;-).  As you see, we're not there yet.

I will be the first to say that the 7.0 optimizer is still in a pretty
crude state: I have made drastic changes to its model of plan costs,
and have not yet had much time to tune the results.  I do appreciate
reports about cases it gets wrong.

In this case I guess the first question to ask is whether its
selectivity estimates are any good.  It seems to be estimating that your
"d0.d0f1 between '1999-11-01' and '1999-12-01'" clause will select about
100 of the 900 rows in dim0; is that anywhere near right?  Also, in the
nested-loop plan we can see that it thinks about 1500 rows from fact1
will match on "d0.d0key = f.d0key" against any given selected row from
dim0; is that on the mark?  Finally, is the estimate that the total
number of joined rows (before GROUP BY) is about 33333 any good?

If you have not done VACUUM ANALYZE recently on these two tables,
it'd be worth trying that to see if it brings the estimates any
closer to reality.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] New Optimizer Behaviour In 7.0b1
  2000-02-26 06:57 New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
  2000-02-26 08:04 ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-02-26 20:23   ` Mark Kirkwood <markir@ihug.co.nz>
  2000-02-26 23:14     ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Mark Kirkwood @ 2000-02-26 20:23 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Tom Lane wrote:

> In this case I guess the first question to ask is whether its
> selectivity estimates are any good.  It seems to be estimating that your
> "d0.d0f1 between '1999-11-01' and '1999-12-01'" clause will select about
> 100 of the 900 rows in dim0; is that anywhere near right?  Also, in the
> nested-loop plan we can see that it thinks about 1500 rows from fact1
> will match on "d0.d0key = f.d0key" against any given selected row from
> dim0; is that on the mark?  Finally, is the estimate that the total
> number of joined rows (before GROUP BY) is about 33333 any good?
>
> If you have not done VACUUM ANALYZE recently on these two tables,
> it'd be worth trying that to see if it brings the estimates any
> closer to reality.
>
>                         regards, tom lane

Tom,

Here is the row data for comparison with the selectivity estimates:

select count(*) from dim0 d0 where d0.d0f1 between '1999-11-01' and
'1999-12-01'               31 rows
select count(*) from fact1 where d0key  =
<value>
3000 rows
total number of joined rows before group
by
9000 rows
( i.e : there are only 3 distinct d0key values in fact1 for the "month" ,
and each one has 3000 rows )

It looks like the estimate on the big table ( fact1 ) are right order of
magnitude, but the small table ( dim0 ) ones are too high (and presumably )
throwing the rest off

I did a vacuum analyze of these tables again, just in case....( no change
to the plans)

Cheers

Mark




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] New Optimizer Behaviour In 7.0b1
  2000-02-26 06:57 New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
  2000-02-26 08:04 ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Tom Lane <tgl@sss.pgh.pa.us>
  2000-02-26 20:23   ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
@ 2000-02-26 23:14     ` Tom Lane <tgl@sss.pgh.pa.us>
  2000-02-27 23:57       ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
  0 siblings, 1 reply; 6+ messages in thread

From: Tom Lane @ 2000-02-26 23:14 UTC (permalink / raw)
  To: Mark Kirkwood <markir@ihug.co.nz>; +Cc: pgsql-sql

Mark Kirkwood <markir@ihug.co.nz> writes:
> It looks like the estimate on the big table ( fact1 ) are right order of
> magnitude, but the small table ( dim0 ) ones are too high (and presumably )
> throwing the rest off

At least part of the problem is that the selectivity code doesn't know
how to deal with date/time datatypes :-(.  I was waiting for Thomas to
commit the great date/time reorganization before I wrote that code.
But he's done now, so I've gone in and fixed convert_to_scalar to know
about all the surviving date/time types.

If you like, you can pick up the updated version of 
src/backend/utils/adt/selfuncs.c from the CVS server, or grab a
snapshot tarball dated later than this message, and see whether
the selectivity estimates get any better.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: [SQL] New Optimizer Behaviour In 7.0b1
  2000-02-26 06:57 New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
  2000-02-26 08:04 ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Tom Lane <tgl@sss.pgh.pa.us>
  2000-02-26 20:23   ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
  2000-02-26 23:14     ` Re: [SQL] New Optimizer Behaviour In 7.0b1 Tom Lane <tgl@sss.pgh.pa.us>
@ 2000-02-27 23:57       ` Mark Kirkwood <markir@ihug.co.nz>
  0 siblings, 0 replies; 6+ messages in thread

From: Mark Kirkwood @ 2000-02-27 23:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql

Tom Lane wrote:

>
> If you like, you can pick up the updated version of
> src/backend/utils/adt/selfuncs.c from the CVS server, or grab a
> snapshot tarball dated later than this message, and see whether
> the selectivity estimates get any better.
>
>                         regards, tom lane

I will give one of those a go....

Cheers

Mark




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2000-02-27 23:57 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-25 03:03 New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
2000-02-26 06:57 New Optimizer Behaviour In 7.0b1 Mark Kirkwood <markir@ihug.co.nz>
2000-02-26 08:04 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-26 20:23   ` Mark Kirkwood <markir@ihug.co.nz>
2000-02-26 23:14     ` Tom Lane <tgl@sss.pgh.pa.us>
2000-02-27 23:57       ` Mark Kirkwood <markir@ihug.co.nz>

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