Thread: index / sequential scan problem

index / sequential scan problem

From
Fabian Kreitner
Date:
Hi all,

Im currently taking my first steps with db optimizations and am wondering
whats happening here and if/how i can help pg choose the better plan.

Thanks,
   Fabian

 >>>

psql (PostgreSQL) 7.2.2

perg_1097=# VACUUM ANALYZE ;
VACUUM
perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#     from    notiz_objekt a
perg_1097-#     where not exists
perg_1097-#     (
perg_1097(#       select  1
perg_1097(#       from    notiz_gelesen b
perg_1097(#       where   ma_id  = 2001
perg_1097(#       and     ma_pid = 1097
perg_1097(#       and     a.notiz_id = b.notiz_id
perg_1097(#     )
perg_1097-# ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=0.00..56125.80 rows=15561 width=12)
(actual time=0.28..2305.52 rows=31122 loops=1)
   SubPlan
     ->  Seq Scan on notiz_gelesen b  (cost=0.00..1.79 rows=1 width=0)
(actual time=0.07..0.07 rows=0 loops=31122)
Total runtime: 2334.42 msec

EXPLAIN
perg_1097=# SET enable_seqscan to false;
SET VARIABLE
perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#     from    notiz_objekt a
perg_1097-#     where not exists
perg_1097-#     (
perg_1097(#       select  1
perg_1097(#       from    notiz_gelesen b
perg_1097(#       where   ma_id  = 2001
perg_1097(#       and     ma_pid = 1097
perg_1097(#       and     a.notiz_id = b.notiz_id
perg_1097(#     )
perg_1097-# ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=100000000.00..100111719.36 rows=15561
width=12) (actual time=0.24..538.86 rows=31122 loops=1)
   SubPlan
     ->  Index Scan using idx_notiz_gelesen_2 on notiz_gelesen
b  (cost=0.00..3.57 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=31122)
Total runtime: 570.75 msec

EXPLAIN
perg_1097=#

perg_1097=# \d notiz_objekt;
       Table "notiz_objekt"
   Column  |  Type   | Modifiers
----------+---------+-----------
  notiz_id | integer |
  obj_id   | integer |
  obj_typ  | integer |
Indexes: idx_notiz_objekt_1,
          idx_notiz_objekt_2

perg_1097=# \d notiz_gelesen;
                                   Table "notiz_gelesen"
   Column  |           Type           |                     Modifiers
----------+--------------------------+----------------------------------------------------
  notiz_id | integer                  |
  ma_id    | integer                  |
  ma_pid   | integer                  |
  stamp    | timestamp with time zone | default ('now'::text)::timestamp(6)
with time zone
  anzeigen | character varying        |
Indexes: idx_notiz_gelesen_1,
          idx_notiz_gelesen_2

perg_1097=#

perg_1097=# select count(*) from notiz_objekt;
  count
-------
  31122
(1 row)

perg_1097=# select count(*) from notiz_gelesen;
  count
-------
     45
(1 row)

perg_1097=#

idx_notiz_gelesen_1 (ma_id,ma_pid)
idx_notiz_gelesen_2 (notiz_id)


Re: index / sequential scan problem

From
"Shridhar Daithankar"
Date:
On 17 Jul 2003 at 11:01, Fabian Kreitner wrote:
> psql (PostgreSQL) 7.2.2
>
> perg_1097=# VACUUM ANALYZE ;
> VACUUM
> perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
> perg_1097-#     from    notiz_objekt a
> perg_1097-#     where not exists
> perg_1097-#     (
> perg_1097(#       select  1
> perg_1097(#       from    notiz_gelesen b
> perg_1097(#       where   ma_id  = 2001
> perg_1097(#       and     ma_pid = 1097
> perg_1097(#       and     a.notiz_id = b.notiz_id
> perg_1097(#     )
> perg_1097-# ;

For 31K records, seq. scan does not sound like a bad plan to me but anyway..

How about

 where   ma_id  = 2001::integer
and     ma_pid = 1097::integer

in above query?

Bye
 Shridhar

--
No one can guarantee the actions of another.        -- Spock, "Day of the Dove",
stardate unknown


Re: index / sequential scan problem

From
Paul Thomas
Date:
On 17/07/2003 10:01 Fabian Kreitner wrote:

Hi Fabian,

When you are doing these kinds of tests, you need to be aware that the
kernel may have most of your data cached after the first query and this
may be why the second query appears to run faster.

Also don't be worried if the planner chooses a seq scan for small tables
as the whole table can often be bought into memory with one IO whereas
reading the index then the table would be 2 IOs.
HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Re: index / sequential scan problem

From
Fabian Kreitner
Date:
At 12:12 17.07.2003, you wrote:

>On 17/07/2003 10:01 Fabian Kreitner wrote:
>
>Hi Fabian,
>
>When you are doing these kinds of tests, you need to be aware that the
>kernel may have most of your data cached after the first query and this
>may be why the second query appears to run faster.

I thought of this too, but executions times wont change with repeating /
alternating these two tests.

>Also don't be worried if the planner chooses a seq scan for small tables
>as the whole table can often be bought into memory with one IO whereas
>reading the index then the table would be 2 IOs. HTH

That is what I read too and is why Im confused that the index is indeed
executing faster. Can this be a problem with the hardware and/or postgress
installation?

Thanks,
   Fabian


Re: index / sequential scan problem

From
Fabian Kreitner
Date:
At 11:17 17.07.2003, Shridhar Daithankar wrote:
>On 17 Jul 2003 at 11:01, Fabian Kreitner wrote:
> > psql (PostgreSQL) 7.2.2
> >
> > perg_1097=# VACUUM ANALYZE ;
> > VACUUM
> > perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
> > perg_1097-#     from    notiz_objekt a
> > perg_1097-#     where not exists
> > perg_1097-#     (
> > perg_1097(#       select  1
> > perg_1097(#       from    notiz_gelesen b
> > perg_1097(#       where   ma_id  = 2001
> > perg_1097(#       and     ma_pid = 1097
> > perg_1097(#       and     a.notiz_id = b.notiz_id
> > perg_1097(#     )
> > perg_1097-# ;
>
>For 31K records, seq. scan does not sound like a bad plan to me but anyway..

Im not generally worried that it uses a seq scan but that the second
example (where an index on the sub select is used on a table with only 45
entries) executes more than 4 times faster. Its not a cache thing either,
since i can enable seqscan again and it will run with 2300ms again.

>How about
>
>  where   ma_id  = 2001::integer
>and     ma_pid = 1097::integer
>
>in above query?

I dont really understand in what way this will help the planner but ill try.

Thanks,
   Fabian


Re: index / sequential scan problem

From
"Shridhar Daithankar"
Date:
On 17 Jul 2003 at 13:12, Fabian Kreitner wrote:

> At 11:17 17.07.2003, Shridhar Daithankar wrote:
> >How about
> >
> >  where   ma_id  = 2001::integer
> >and     ma_pid = 1097::integer
> >
> >in above query?
>
> I dont really understand in what way this will help the planner but ill try.

That is typecasting. It helps planner understand query in more correct fashion.

Bye
 Shridhar

--
QOTD:    "I may not be able to walk, but I drive from the sitting posistion."


Re: index / sequential scan problem

From
Paul Thomas
Date:
On 17/07/2003 12:13 Fabian Kreitner wrote:
> That is what I read too and is why Im confused that the index is indeed
> executing faster. Can this be a problem with the hardware and/or
> postgress installation?


It's more likely that the OS has most of the data cached after the first
query and so doesn't need to re-read that data from disk when you retry
the query with seq scan disabled. Try something like this:

set enable_seqscan to true;
explain analyze ......
set enable_seqscan to false;
explain analyze ......
set enable_seqscan to true;
explain analyze ......

I expect you will find that the third query is also a lot faster that the
first query.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Re: index / sequential scan problem

From
Fabian Kreitner
Date:
At 14:34 17.07.2003, you wrote:

>On 17/07/2003 12:13 Fabian Kreitner wrote:
>>That is what I read too and is why Im confused that the index is indeed
>>executing faster. Can this be a problem with the hardware and/or
>>postgress installation?
>
>
>It's more likely that the OS has most of the data cached after the first
>query and so doesn't need to re-read that data from disk when you retry
>the query with seq scan disabled. Try something like this:
>
>set enable_seqscan to true;
>explain analyze ......
>set enable_seqscan to false;
>explain analyze ......
>set enable_seqscan to true;
>explain analyze ......
>
>I expect you will find that the third query is also a lot faster that the
>first query.

Im afraid, no.
Database has been stopped / started right before this.

perg_1097=# set enable_seqscan to true;
SET VARIABLE
perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#      from    notiz_objekt a
perg_1097-#      where not exists
perg_1097-#      (
perg_1097(#        select  1
perg_1097(#        from    notiz_gelesen b
perg_1097(#        where   ma_id  = 2001
perg_1097(#        and     ma_pid = 1097
perg_1097(#        and     a.notiz_id = b.notiz_id
perg_1097(#      )
perg_1097-#  ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=0.00..56125.80 rows=15561 width=12)
(actual time=0.28..2298.71 rows=31122 loops=1)
   SubPlan
     ->  Seq Scan on notiz_gelesen b  (cost=0.00..1.79 rows=1 width=0)
(actual time=0.07..0.07 rows=0 loops=31122)
Total runtime: 2327.37 msec

EXPLAIN
perg_1097=# set enable_seqscan to false;
SET VARIABLE
perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#      from    notiz_objekt a
perg_1097-#      where not exists
perg_1097-#      (
perg_1097(#        select  1
perg_1097(#        from    notiz_gelesen b
perg_1097(#        where   ma_id  = 2001
perg_1097(#        and     ma_pid = 1097
perg_1097(#        and     a.notiz_id = b.notiz_id
perg_1097(#      )
perg_1097-#  ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=100000000.00..100111719.36 rows=15561
width=12) (actual time=0.25..535.75 rows=31122 loops=1)
   SubPlan
     ->  Index Scan using idx_notiz_gelesen_2 on notiz_gelesen
b  (cost=0.00..3.57 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=31122)
Total runtime: 567.94 msec

EXPLAIN
perg_1097=# set enable_seqscan to true;
SET VARIABLE
perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#      from    notiz_objekt a
perg_1097-#      where not exists
perg_1097-#      (
perg_1097(#        select  1
perg_1097(#        from    notiz_gelesen b
perg_1097(#        where   ma_id  = 2001
perg_1097(#        and     ma_pid = 1097
perg_1097(#        and     a.notiz_id = b.notiz_id
perg_1097(#      )
perg_1097-#  ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=0.00..56125.80 rows=15561 width=12)
(actual time=0.13..2300.74 rows=31122 loops=1)
   SubPlan
     ->  Seq Scan on notiz_gelesen b  (cost=0.00..1.79 rows=1 width=0)
(actual time=0.07..0.07 rows=0 loops=31122)
Total runtime: 2330.25 msec

EXPLAIN
perg_1097=#


Re: index / sequential scan problem

From
"Shridhar Daithankar"
Date:
On 17 Jul 2003 at 14:50, Fabian Kreitner wrote:

> At 14:34 17.07.2003, you wrote:
> >I expect you will find that the third query is also a lot faster that the
> >first query.
>
> Im afraid, no.
> Database has been stopped / started right before this.
>
> perg_1097=# set enable_seqscan to true;
> SET VARIABLE
> perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
> perg_1097-#      from    notiz_objekt a
> perg_1097-#      where not exists

Well, he said query and not the query plan...:-)

While explain analyze is great for judging what is happening, it's not always a
good idea to trust the numbers produced by it. It will probably produce same
number for a SCSI disk machine and for a IDE disk machine, everything else
being equal. Obviously that is not correct.

Only thing you can trust in explain analyze is it's plan. Numbers are based on
heuristic and should be taken as hint only.

Bye
 Shridhar

--
Harrisberger's Fourth Law of the Lab:    Experience is directly proportional to
the amount of equipment ruined.


Re: index / sequential scan problem

From
Jord Tanner
Date:
I've seen similar behavior in my own queries. I found that reducing
random_page_cost from the default of 4 down to 2 caused the query to
choose the index, and resulted in an order of magnitude improvement on
some queries.

On Thu, 2003-07-17 at 05:50, Fabian Kreitner wrote:
> At 14:34 17.07.2003, you wrote:
>
> >On 17/07/2003 12:13 Fabian Kreitner wrote:
> >>That is what I read too and is why Im confused that the index is indeed
> >>executing faster. Can this be a problem with the hardware and/or
> >>postgress installation?
> >
> >
> >It's more likely that the OS has most of the data cached after the first
> >query and so doesn't need to re-read that data from disk when you retry
> >the query with seq scan disabled. Try something like this:
> >
> >set enable_seqscan to true;
> >explain analyze ......
> >set enable_seqscan to false;
> >explain analyze ......
> >set enable_seqscan to true;
> >explain analyze ......
> >
> >I expect you will find that the third query is also a lot faster that the
> >first query.
>
> Im afraid, no.
> Database has been stopped / started right before this.
>
> perg_1097=# set enable_seqscan to true;
> SET VARIABLE
> perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
> perg_1097-#      from    notiz_objekt a
> perg_1097-#      where not exists
> perg_1097-#      (
> perg_1097(#        select  1
> perg_1097(#        from    notiz_gelesen b
> perg_1097(#        where   ma_id  = 2001
> perg_1097(#        and     ma_pid = 1097
> perg_1097(#        and     a.notiz_id = b.notiz_id
> perg_1097(#      )
> perg_1097-#  ;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on notiz_objekt a  (cost=0.00..56125.80 rows=15561 width=12)
> (actual time=0.28..2298.71 rows=31122 loops=1)
>    SubPlan
>      ->  Seq Scan on notiz_gelesen b  (cost=0.00..1.79 rows=1 width=0)
> (actual time=0.07..0.07 rows=0 loops=31122)
> Total runtime: 2327.37 msec
>
> EXPLAIN
> perg_1097=# set enable_seqscan to false;
> SET VARIABLE
> perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
> perg_1097-#      from    notiz_objekt a
> perg_1097-#      where not exists
> perg_1097-#      (
> perg_1097(#        select  1
> perg_1097(#        from    notiz_gelesen b
> perg_1097(#        where   ma_id  = 2001
> perg_1097(#        and     ma_pid = 1097
> perg_1097(#        and     a.notiz_id = b.notiz_id
> perg_1097(#      )
> perg_1097-#  ;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on notiz_objekt a  (cost=100000000.00..100111719.36 rows=15561
> width=12) (actual time=0.25..535.75 rows=31122 loops=1)
>    SubPlan
>      ->  Index Scan using idx_notiz_gelesen_2 on notiz_gelesen
> b  (cost=0.00..3.57 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=31122)
> Total runtime: 567.94 msec
>
> EXPLAIN
> perg_1097=# set enable_seqscan to true;
> SET VARIABLE
> perg_1097=# EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
> perg_1097-#      from    notiz_objekt a
> perg_1097-#      where not exists
> perg_1097-#      (
> perg_1097(#        select  1
> perg_1097(#        from    notiz_gelesen b
> perg_1097(#        where   ma_id  = 2001
> perg_1097(#        and     ma_pid = 1097
> perg_1097(#        and     a.notiz_id = b.notiz_id
> perg_1097(#      )
> perg_1097-#  ;
> NOTICE:  QUERY PLAN:
>
> Seq Scan on notiz_objekt a  (cost=0.00..56125.80 rows=15561 width=12)
> (actual time=0.13..2300.74 rows=31122 loops=1)
>    SubPlan
>      ->  Seq Scan on notiz_gelesen b  (cost=0.00..1.79 rows=1 width=0)
> (actual time=0.07..0.07 rows=0 loops=31122)
> Total runtime: 2330.25 msec
>
> EXPLAIN
> perg_1097=#
>
>
> ---------------------------(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
--
Jord Tanner <jord@indygecko.com>


Re: index / sequential scan problem

From
Paul Thomas
Date:
On 17/07/2003 13:50 Fabian Kreitner wrote:
> [snip]
> Im afraid, no.
> Database has been stopped / started right before this.
> [snip]

1) enable_seqscan = true
> Seq Scan on notiz_objekt a  (cost=0.00..56125.80 rows=15561 width=12)
> (actual time=0.28..2298.71 rows=31122 loops=1)
> [snip]

2) enable_seqscan = false
> Seq Scan on notiz_objekt a  (cost=100000000.00..100111719.36 rows=15561
> width=12) (actual time=0.25..535.75 rows=31122 loops=1)

I've just noticed this. Something is not right here. Look at the crazy
cost estimation for the second query. It looks to me like
enable_indexscan, enable_tidscan, enable_sort, enable_nestloop,
enable_mergejoin or enable_hashjoin have been set to false. Looking at the
source, thats the only way I can see that such large numbers can be
produced.

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for the Smaller
Business |
| Computer Consultants         |
http://www.thomas-micro-systems-ltd.co.uk   |
+------------------------------+---------------------------------------------+

Re: index / sequential scan problem

From
Tom Lane
Date:
Paul Thomas <paul@tmsl.demon.co.uk> writes:
> 2) enable_seqscan = false
>> Seq Scan on notiz_objekt a  (cost=100000000.00..100111719.36 rows=15561
>> width=12) (actual time=0.25..535.75 rows=31122 loops=1)

> I've just noticed this. Something is not right here. Look at the crazy
> cost estimation for the second query.

No, that's exactly what it's supposed to do.  enable_seqscan cannot
simply suppress generation of a seqscan plan (because that might be
the only way to do the query, if there's no applicable index).  So it
generates the plan, but sticks a large penalty into the cost estimate
to keep the planner from choosing that alternative if there is any
other.  The "100000000.00" is that artificial penalty.

We could probably hide this implementation detail from you if we tried
hard enough, but it hasn't bothered anyone enough to try.

            regards, tom lane

Re: index / sequential scan problem

From
Tom Lane
Date:
Fabian Kreitner <fabian.kreitner@ainea-ag.de> writes:
> That is what I read too and is why Im confused that the index is indeed
> executing faster. Can this be a problem with the hardware and/or postgress
> installation?

I think the actual issue here is that you are executing the EXISTS
subplan over and over, once for each outer row.  The planner's cost
estimate for EXISTS is based on the assumption that you do it once
... in which scenario the seqscan very possibly is cheaper.  However,
when you do the EXISTS subplan over and over for many outer rows, you
get a savings from the fact that the index and table pages soon get
cached in memory.  The seqscan plan gets a savings too, since the table
is small enough to fit in memory, but once everything is in memory the
indexscan plan is faster.

There's been some discussion on pghackers about how to teach the planner
to account for repeated executions of subplans, but we have not come up
with a good solution yet.

For the moment, what people tend to do if they know their database is
small enough to mostly stay in memory is to reduce random_page_cost to
make the planner favor indexscans.  If you know the database is entirely
cached then the theoretically correct value of random_page_cost is 1.0
(since fetching any page will cost the same, if it's all in RAM).  I'd
recommend against adopting that as a default, but a lot of people find
that setting it to 2.0 or so seems to model their situation better than
the out-of-the-box 4.0.

            regards, tom lane

Re: index / sequential scan problem

From
Fabian Kreitner
Date:
At 20:12 17.07.2003, Tom Lane wrote:
>Fabian Kreitner <fabian.kreitner@ainea-ag.de> writes:
> > That is what I read too and is why Im confused that the index is indeed
> > executing faster. Can this be a problem with the hardware and/or postgress
> > installation?
>
>I think the actual issue here is that you are executing the EXISTS
>subplan over and over, once for each outer row.  The planner's cost
>estimate for EXISTS is based on the assumption that you do it once
>... in which scenario the seqscan very possibly is cheaper.  However,
>when you do the EXISTS subplan over and over for many outer rows, you
>get a savings from the fact that the index and table pages soon get
>cached in memory.  The seqscan plan gets a savings too, since the table
>is small enough to fit in memory, but once everything is in memory the
>indexscan plan is faster.
>
>There's been some discussion on pghackers about how to teach the planner
>to account for repeated executions of subplans, but we have not come up
>with a good solution yet.
>
>For the moment, what people tend to do if they know their database is
>small enough to mostly stay in memory is to reduce random_page_cost to
>make the planner favor indexscans.  If you know the database is entirely
>cached then the theoretically correct value of random_page_cost is 1.0
>(since fetching any page will cost the same, if it's all in RAM).  I'd
>recommend against adopting that as a default, but a lot of people find
>that setting it to 2.0 or so seems to model their situation better than
>the out-of-the-box 4.0.

Thanks for the explanation :)


However .... :(

perg_1097=# vacuum analyze;
VACUUM
perg_1097=# set random_page_cost to 1.0;
SET VARIABLE
perg_1097=#  EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#       from    notiz_objekt a
perg_1097-#       where not exists
perg_1097-#      (
perg_1097(#        select  1
perg_1097(#        from    notiz_gelesen b
perg_1097(#        where   ma_id  = 2001
perg_1097(#        and     ma_pid = 1097
perg_1097(#        and     a.notiz_id = b.notiz_id
perg_1097(#      )
perg_1097-#   ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=0.00..56125.80 rows=15561 width=12)
(actual time=0.27..2299.09 rows=31122 loops=1)
   SubPlan
     ->  Seq Scan on notiz_gelesen b  (cost=0.00..1.79 rows=1 width=0)
(actual time=0.07..0.07 rows=0 loops=31122)
Total runtime: 2328.05 msec

EXPLAIN
perg_1097=#

...

perg_1097=# set enable_seqscan to false;
SET VARIABLE
perg_1097=# set random_page_cost to 1.0;
SET VARIABLE
perg_1097=#  EXPLAIN ANALYZE    select  notiz_id, obj_id, obj_typ
perg_1097-#       from    notiz_objekt a
perg_1097-#       where not exists
perg_1097-#      (
perg_1097(#        select  1
perg_1097(#        from    notiz_gelesen b
perg_1097(#        where   ma_id  = 2001
perg_1097(#        and     ma_pid = 1097
perg_1097(#        and     a.notiz_id = b.notiz_id
perg_1097(#      )
perg_1097-#   ;
NOTICE:  QUERY PLAN:

Seq Scan on notiz_objekt a  (cost=100000000.00..100093380.36 rows=15561
width=12) (actual time=0.07..550.07 rows=31122 loops=1)
   SubPlan
     ->  Index Scan using idx_notiz_gelesen_2 on notiz_gelesen
b  (cost=0.00..2.98 rows=1 width=0) (actual time=0.01..0.01 rows=0 loops=31122)
Total runtime: 582.90 msec

EXPLAIN
perg_1097=#


Even with a random page cost of 1 it thinks using the index should/could
take significantly longer which it doesnt for some reason :-/


Re: index / sequential scan problem

From
Fabian Kreitner
Date:
Hi all,

Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index.

Anything I need to consider when raising it to such "high" values?

Thanks for the help,
   Fabian


Re: index / sequential scan problem

From
Dennis Björklund
Date:
On Fri, 18 Jul 2003, Fabian Kreitner wrote:

> Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index.

Doesn't sound very good and it will most likely make other queries slower.
You could always turn off sequential scan before that query and turn it on
after.

> Anything I need to consider when raising it to such "high" values?

You could fill the table with more data and it will probably come to a
point where it will stop using the seq. scan.

You could of course also change pg itself so it calculates a better
estimate.

--
/Dennis


Re: index / sequential scan problem

From
Tom Lane
Date:
=?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org> writes:
> On Fri, 18 Jul 2003, Fabian Kreitner wrote:
>> Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index.

> Doesn't sound very good and it will most likely make other queries slower.

Seems like a reasonable approach to me --- certainly better than setting
random_page_cost to physically nonsensical values.

In a fully-cached situation it's entirely reasonable to inflate the
various cpu_xxx costs, since by assumption you are not paying the normal
price of physical disk I/O.  Fetching a page from kernel buffer cache
is certainly cheaper than getting it off the disk.  But the CPU costs
involved in processing the page contents don't change.  Since our cost
unit is defined as 1.0 = one sequential page fetch, you have to increase
the cpu_xxx numbers instead of reducing the I/O cost estimate.

I would recommend inflating all the cpu_xxx costs by the same factor,
unless you have evidence that they are wrong in relation to each other.

            regards, tom lane

Re: index / sequential scan problem

From
"scott.marlowe"
Date:
On Fri, 18 Jul 2003, Tom Lane wrote:

> =?ISO-8859-1?Q?Dennis_Bj=F6rklund?= <db@zigo.dhs.org> writes:
> > On Fri, 18 Jul 2003, Fabian Kreitner wrote:
> >> Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index.
>
> > Doesn't sound very good and it will most likely make other queries slower.
>
> Seems like a reasonable approach to me --- certainly better than setting
> random_page_cost to physically nonsensical values.
>
> In a fully-cached situation it's entirely reasonable to inflate the
> various cpu_xxx costs, since by assumption you are not paying the normal
> price of physical disk I/O.  Fetching a page from kernel buffer cache
> is certainly cheaper than getting it off the disk.  But the CPU costs
> involved in processing the page contents don't change.  Since our cost
> unit is defined as 1.0 = one sequential page fetch, you have to increase
> the cpu_xxx numbers instead of reducing the I/O cost estimate.
>
> I would recommend inflating all the cpu_xxx costs by the same factor,
> unless you have evidence that they are wrong in relation to each other.

And don't forget to set effective_cache_size.  It's the one I missed for
the longest when I started.


Re: index / sequential scan problem

From
Dennis Björklund
Date:
On Fri, 18 Jul 2003, Tom Lane wrote:

> >> Adjusting the cpu_tuple_cost to 0.042 got the planner to choose the index.
>
> > Doesn't sound very good and it will most likely make other queries slower.
>
> Seems like a reasonable approach to me --- certainly better than setting
> random_page_cost to physically nonsensical values.

Hehe, just before this letter there was talk about changing
random_page_cost. I kind of responed that 0.042 is not a good random page
cost. But now of course I can see that it says cpu_tuple_cost :-)

Sorry for adding confusion.

--
/Dennis