pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
cpu_tuple_cost
20+ messages / 11 participants
[nested] [flat]

* cpu_tuple_cost
@ 2005-03-11 14:25  Daniel Schuchardt <daniel_schuchardt@web.de>
  0 siblings, 2 replies; 20+ messages in thread

From: Daniel Schuchardt @ 2005-03-11 14:25 UTC (permalink / raw)
  To: pgsql-performance

Hi List,

i have a query plan who is bad with standard cpu_tuple_costs and good if 
I raise cpu_tuple_costs. Is it is a good practice to raise them if i 
want to force postgres to use indexes more often? Or is it is better to 
disable sequence scans?

CIMSOFT=# ANALYSE mitpln;
ANALYZE

CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM mitpln WHERE 
date_to_yearmonth_dec(mpl_date)='20050';
                                                   QUERY PLAN

--------------------------------------------------------------------------------
  Seq Scan on mitpln  (cost=0.00..1411.85 rows=2050 width=69) (actual 
time=562.000..1203.000 rows=1269 loops=1)
    Filter: ((date_to_yearmonth_dec((mpl_date)::timestamp without time 
zone))::text = '20050'::text)
  Total runtime: 1203.000 ms
(3 rows)

CIMSOFT=# SET cpu_tuple_cost = 0.07;
SET
CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM mitpln WHERE 
date_to_yearmonth_dec(mpl_date)='20050';
                                                               QUERY PLAN

--------------------------------------------------------------------------------
  Index Scan using mitpln_yearmonth_dec on mitpln  (cost=0.00..2962.86 
rows=2050width=69) (actual time=0.000..0.000 rows=1269 loops=1)
    Index Cond: ((date_to_yearmonth_dec((mpl_date)::timestamp without 
time zone))::text = '20050'::text)
  Total runtime: 16.000 ms
(3 rows)


CIMSOFT=# \d mitpln
                                        Table "public.mitpln"
     Column    |         Type          |                         Modifiers

--------------+-----------------------+-----------------------------------------
  mpl_id       | integer               | not null default 
nextval('public.mitpln_mpl_id_seq'::text)
  mpl_date     | date                  |
  mpl_minr     | integer               | not null
  mpl_tpl_name | character varying(20) |
  mpl_feiertag | character varying(50) |
  mpl_min      | real                  |
  mpl_saldo    | real                  |
  mpl_buch     | boolean               | not null default false
  mpl_absaldo  | real                  |
  mpl_vhz      | real                  |
  dbrid        | character varying     | default nextval('db_id_seq'::text)
Indexes:
     "mitpln_pkey" PRIMARY KEY, btree (mpl_id)
     "mitpln_idindex" UNIQUE, btree (dbrid)
     "xtt5126" UNIQUE, btree (mpl_date, mpl_minr)
     "mitpln_yearmonth_dec" btree 
(date_to_yearmonth_dec(mpl_date::timestamp with
out time zone))


CIMSOFT=# SELECT count(*) FROM mitpln;
  count
-------
  26128
(1 row)



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

* Re: cpu_tuple_cost
@ 2005-03-11 14:27  Daniel Schuchardt <daniel_schuchardt@web.de>
  parent: Daniel Schuchardt <daniel_schuchardt@web.de>
  1 sibling, 0 replies; 20+ messages in thread

From: Daniel Schuchardt @ 2005-03-11 14:27 UTC (permalink / raw)
  To: pgsql-performance

I have forgotten this :

CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS 
VARCHAR AS'
BEGIN
  RETURN extract(year FROM $1) || extract(month FROM $1)-1;
END'LANGUAGE plpgsql IMMUTABLE;



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

* Re: cpu_tuple_cost
@ 2005-03-14 06:29  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Daniel Schuchardt <daniel_schuchardt@web.de>
  1 sibling, 2 replies; 20+ messages in thread

From: Tom Lane @ 2005-03-14 06:29 UTC (permalink / raw)
  To: Daniel Schuchardt <daniel_schuchardt@web.de>; +Cc: pgsql-performance

Daniel Schuchardt <daniel_schuchardt@web.de> writes:
> i have a query plan who is bad with standard cpu_tuple_costs and good if 
> I raise cpu_tuple_costs. Is it is a good practice to raise them if i 
> want to force postgres to use indexes more often?

Reducing random_page_cost is usually the best way to get the planner to
favor indexscans more.

			regards, tom lane



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

* Re: cpu_tuple_cost
@ 2005-03-15 02:05  Greg Sabino Mullane <greg@turnstep.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 3 replies; 20+ messages in thread

From: Greg Sabino Mullane @ 2005-03-15 02:05 UTC (permalink / raw)
  To: pgsql-performance


-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> Reducing random_page_cost is usually the best way to get the
> planner to favor indexscans more.

On that note, can I raise the idea again of dropping the default
value for random_page_cost in postgresql.conf? I think 4 is too
conservative in this day and age. Certainly the person who will
be negatively impacted by a default drop of 4 to 3 will be the
exception and not the rule.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200503140702
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFCNX2avJuQZxSWSsgRAk7QAJ4lye7pEcQIWMRV2fs15bHGY2zBbACeJtLC
E/vUG/lagjcyWPt9gfngsn0=
=CKIq
-----END PGP SIGNATURE-----





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

* Re: cpu_tuple_cost
@ 2005-03-15 02:17  Bruce Momjian <pgman@candle.pha.pa.us>
  parent: Greg Sabino Mullane <greg@turnstep.com>
  2 siblings, 0 replies; 20+ messages in thread

From: Bruce Momjian @ 2005-03-15 02:17 UTC (permalink / raw)
  To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-performance

Greg Sabino Mullane wrote:
[ There is text before PGP section. ]
> 
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
> 
> 
> > Reducing random_page_cost is usually the best way to get the
> > planner to favor indexscans more.
> 
> On that note, can I raise the idea again of dropping the default
> value for random_page_cost in postgresql.conf? I think 4 is too
> conservative in this day and age. Certainly the person who will
> be negatively impacted by a default drop of 4 to 3 will be the
> exception and not the rule.

Agreed.  I think we should reduce it at least to 3.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



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

* Re: cpu_tuple_cost
@ 2005-03-15 02:23  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Greg Sabino Mullane <greg@turnstep.com>
  2 siblings, 1 reply; 20+ messages in thread

From: Tom Lane @ 2005-03-15 02:23 UTC (permalink / raw)
  To: Greg Sabino Mullane <greg@turnstep.com>; +Cc: pgsql-performance

"Greg Sabino Mullane" <greg@turnstep.com> writes:
> On that note, can I raise the idea again of dropping the default
> value for random_page_cost in postgresql.conf? I think 4 is too
> conservative in this day and age. Certainly the person who will
> be negatively impacted by a default drop of 4 to 3 will be the
> exception and not the rule.

The ones who'd be negatively impacted are the ones we haven't
been hearing from ;-).  To assume that they aren't out there
is a logical fallacy.

I still think that 4 is about right for large databases (where
"large" is in comparison to available RAM).

Also, to the extent that we think these numbers mean anything at all,
we should try to keep them matching the physical parameters we think
they represent.  I think that the "reduce random_page_cost" mantra
is not an indication that that parameter is wrong, but that the
cost models it feeds into need more work.  One thing we *know*
is wrong is the costing of nestloop inner indexscans: there needs
to be a correction for caching of index blocks across repeated
scans.  I've looked at this a few times but not come up with
anything that seemed convincing.  Another thing I've wondered
about more than once is if we shouldn't discount fetching of
higher-level btree pages on the grounds that they're probably
in RAM already, even if the indexscan isn't inside a loop.

			regards, tom lane



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

* Re: cpu_tuple_cost
@ 2005-03-15 05:30  Gregory Stark <gsstark@mit.edu>
  0 siblings, 1 reply; 20+ messages in thread

From: Gregory Stark @ 2005-03-15 05:30 UTC (permalink / raw)
  To: pgsql-performance


Bruce Momjian <pgman@candle.pha.pa.us> wrote:

> Agreed.  I think we should reduce it at least to 3.

Note that changing it from 4 to 3 or even 2 is unlikely to really change much.
Many of the plans people complain about turn out to have critical points
closer to 1.2 or 1.1. 

The only reason things work out better with such low values is because people
have data sets that fit more or less entirely in RAM. So values close to 1 or
even equal to 1 actually represent the reality.

The "this day and age" argument isn't very convincing. Hard drive capacity
growth has far outstripped hard drive seek time and bandwidth improvements.
Random access has more penalty than ever.

-- 
greg




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

* Re: cpu_tuple_cost
@ 2005-03-15 06:10  Josh Berkus <josh@agliodbs.com>
  parent: Greg Sabino Mullane <greg@turnstep.com>
  2 siblings, 0 replies; 20+ messages in thread

From: Josh Berkus @ 2005-03-15 06:10 UTC (permalink / raw)
  To: pgsql-performance

Greg,

> On that note, can I raise the idea again of dropping the default
> value for random_page_cost in postgresql.conf? I think 4 is too
> conservative in this day and age. Certainly the person who will
> be negatively impacted by a default drop of 4 to 3 will be the
> exception and not the rule.

I don't agree.  The defaults are there for people who aren't going to read 
enough of the documentation to set them.  As such, conservative for the 
defaults is appropriate.

If we were going to change anything automatically, it would be to set 
effective_cache_size to 1/3 of RAM at initdb time.  However, I don't know any 
method to determine RAM size that works on all the platforms we support.

Tom,

> Also, to the extent that we think these numbers mean anything at all,
> we should try to keep them matching the physical parameters we think
> they represent. 

Personally, what I would love to see is the system determining and caching 
some of these parameters automatically.   For example, in a database which 
has been running in production for a couple of days, it should be possible to 
determine the ratio of average random seek tuple cost to average seq scan 
tuple cost.

Other parameters should really work the same way.   Effective_cache_size, for 
example, is a blunt instrument to replace what the database should ideally do 
through automated interactive fine tuning.  Particularly since we have 2 
separate caches (or 3, if you count t1 and t2 from 2Q).   What the planner 
really needs to know is: is this table or index already in the t1 or t2 cache 
(can't we determine this?)?   How likely is it to be in the filesystem cache?  
The latter question is not just one of size (table < memory), but one of 
frequency of access.

Of course, this stuff is really, really hard which is why we rely on the 
GUCs ...

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



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

* Re: cpu_tuple_cost
@ 2005-03-15 23:41  David Brown <time@bigpond.net.au>
  parent: Gregory Stark <gsstark@mit.edu>
  0 siblings, 1 reply; 20+ messages in thread

From: David Brown @ 2005-03-15 23:41 UTC (permalink / raw)
  To: pgsql-performance

Gregory Stark wrote:

>The "this day and age" argument isn't very convincing. Hard drive capacity
>growth has far outstripped hard drive seek time and bandwidth improvements.
>Random access has more penalty than ever.
>  
>
In point of fact, there haven't been noticeable seek time improvements 
for years. Transfer rates, on the other hand, have gone through the roof.

Which is why I would question the published tuning advice that 
recommends lowering it to 2 for arrays. Arrays increase the effective 
transfer rate more than they reduce random access times. Dropping from 4 
to 2 would reflect going from a typical single 7200rpm ATA drive to a 
15000rpm SCSI drive, but striping will move it back up again - probably 
even higher than 4 with a big array (at a guess, perhaps the 
relationship might be approximated as a square root after allowing for 
the array type?).

With default settings, I've seen the planner pick the wrong index unless 
random_page_cost was set to 2. But in testing on an ATA drive, I 
achieved slightly better plan costings by increasing cpu_tuple_cost 
(relative to cpu_index_tuple_cost - by default it's only a factor of 10) 
and actually *raising* random_page_cost to 5! So why pick on one 
parameter? It's all going to vary according to the query and the data.

I agree with Tom 100%. Pulling levers on a wonky model is no solution.



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

* Re: cpu_tuple_cost
@ 2005-03-16 07:23  Greg Stark <gsstark@mit.edu>
  parent: David Brown <time@bigpond.net.au>
  0 siblings, 1 reply; 20+ messages in thread

From: Greg Stark @ 2005-03-16 07:23 UTC (permalink / raw)
  To: David Brown <time@bigpond.net.au>; +Cc: pgsql-performance


David Brown <time@bigpond.net.au> writes:

> Gregory Stark wrote:
> 
> >The "this day and age" argument isn't very convincing. Hard drive capacity
> >growth has far outstripped hard drive seek time and bandwidth improvements.
> >Random access has more penalty than ever.
>
> In point of fact, there haven't been noticeable seek time improvements for
> years. Transfer rates, on the other hand, have gone through the roof.

Er, yeah. I stated it wrong. The real ratio here is between seek time and
throughput.

Typical 7200RPM drives have average seek times are in the area of 10ms.
Typical sustained transfer rates are in the range of 40Mb/s. Postgres reads
8kB blocks at a time.

So 800kB/s for random access reads. And 40Mb/s for sequential reads. That's a
factor of 49. I don't think anyone wants random_page_cost to be set to 50
though.

For a high end 15k drive I see average seek times get as low as 3ms. And
sustained transfer rates get as high as 100Mb/s. So about 2.7Mb/s for random
access reads or about a random_page_cost of 37. Still pretty extreme.

So what's going on with the empirically derived value of 4? Perhaps this is
because even though Postgres is reading an entire table sequentially it's
unlikely to be the only I/O consumer? The sequential reads would be
interleaved occasionally by some other I/O forcing a seek to continue.

In which case the true random_page_cost seems like it would be extremely
sensitive to the amount of readahead the OS does. To reach a random_page_cost
of 4 given the numbers above for a 7200RPM drive requires that just under 25%
of the I/O of a sequential table scan be random seeks [*]. That translates to
32kB of sequential reading, which actually does sound like a typical value for
OS readahead.

I wonder if those same empirical tests would show even higher values of
random_page_cost if the readahead were turned up to 64kB or 128kB.




[*] A bit of an algebraic diversion: 

    1s/10ms = 100 random buffers/s. 
    random_page_cost = 4 so net sequential buffers/s = 400.

    solve:

    400 buffers = rnd+seq
    1000ms = .2*seq + 10*rnd


-- 
greg




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

* Re: cpu_tuple_cost
@ 2005-03-16 09:42  Magnus Hagander <mha@sollentuna.net>
  0 siblings, 1 reply; 20+ messages in thread

From: Magnus Hagander @ 2005-03-16 09:42 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; David Brown <time@bigpond.net.au>; +Cc: pgsql-performance

> > >The "this day and age" argument isn't very convincing. Hard drive 
> > >capacity growth has far outstripped hard drive seek time 
> and bandwidth improvements.
> > >Random access has more penalty than ever.
> >
> > In point of fact, there haven't been noticeable seek time 
> improvements 
> > for years. Transfer rates, on the other hand, have gone 
> through the roof.
> 
> Er, yeah. I stated it wrong. The real ratio here is between 
> seek time and throughput.
> 
> Typical 7200RPM drives have average seek times are in the 
> area of 10ms.
> Typical sustained transfer rates are in the range of 40Mb/s. 
> Postgres reads 8kB blocks at a time.
> 
> So 800kB/s for random access reads. And 40Mb/s for sequential 
> reads. That's a factor of 49. I don't think anyone wants 
> random_page_cost to be set to 50 though.
> 
> For a high end 15k drive I see average seek times get as low 
> as 3ms. And sustained transfer rates get as high as 100Mb/s. 
> So about 2.7Mb/s for random access reads or about a 
> random_page_cost of 37. Still pretty extreme.
> 
> So what's going on with the empirically derived value of 4? 
> Perhaps this is because even though Postgres is reading an 
> entire table sequentially it's unlikely to be the only I/O 
> consumer? The sequential reads would be interleaved 
> occasionally by some other I/O forcing a seek to continue.

What about the cache memory on the disk? Even IDE disks have some 8Mb
cache today, which makes a lot of difference for fairly short scans.
Even if it's just read cache. That'll bring the speed of random access
down to a 1=1 relationship with sequential access, assuming all fits in
the cache.


//Magnus



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

* Re: cpu_tuple_cost
@ 2005-03-16 15:42  Greg Stark <gsstark@mit.edu>
  parent: Magnus Hagander <mha@sollentuna.net>
  0 siblings, 0 replies; 20+ messages in thread

From: Greg Stark @ 2005-03-16 15:42 UTC (permalink / raw)
  To: Magnus Hagander <mha@sollentuna.net>; +Cc: Greg Stark <gsstark@mit.edu>; David Brown <time@bigpond.net.au>; pgsql-performance

"Magnus Hagander" <mha@sollentuna.net> writes:

> What about the cache memory on the disk? Even IDE disks have some 8Mb
> cache today, which makes a lot of difference for fairly short scans.
> Even if it's just read cache. That'll bring the speed of random access
> down to a 1=1 relationship with sequential access, assuming all fits in
> the cache.

8MB cache is really insignificant compared to the hundreds or thousands of
megabytes the OS would be using to cache. You could just add the 8MB to your
effective_cache_size (except it's not really 100% effective since it would
contain some of the same blocks as the OS cache).

-- 
greg




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

* Re: cpu_tuple_cost
@ 2005-03-16 18:03  Josh Berkus <josh@agliodbs.com>
  parent: Greg Stark <gsstark@mit.edu>
  0 siblings, 2 replies; 20+ messages in thread

From: Josh Berkus @ 2005-03-16 18:03 UTC (permalink / raw)
  To: Greg Stark <gsstark@mit.edu>; +Cc: David Brown <time@bigpond.net.au>; pgsql-performance

Greg,

> So 800kB/s for random access reads. And 40Mb/s for sequential reads. That's
> a factor of 49. I don't think anyone wants random_page_cost to be set to 50
> though.
>
> For a high end 15k drive I see average seek times get as low as 3ms. And
> sustained transfer rates get as high as 100Mb/s. So about 2.7Mb/s for
> random access reads or about a random_page_cost of 37. Still pretty
> extreme.

Actually, what you're demonstrating here is that there's really no point in 
having a random_page_cost GUC, since the seek/scan ratio is going to be high 
regardless.   

Although I can point out that you left out the fact that the disk needs to do 
a seek to find the beginning of the seq scan area, and even then some file 
fragmentation is possible.   Finally, I've never seen PostgreSQL manage more 
than 70% of the maximum read rate, and in most cases more like 30%.     

> So what's going on with the empirically derived value of 4? 

It's not empirically derived; it's a value we plug into an 
internal-to-postgresql formula.   And "4" is a fairly conservative value that 
works for a lot of systems.

Realistically, the values we should be deriving from are:
-- median file cache size for postgresql files
-- average disk read throughput
-- effective processor calculation throughput
-- median I/O contention

However, working those 4 hardware "facts" into forumulas that allow us to 
calculate the actual cost of a query execution plan is somebody's PhD paper.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



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

* Re: cpu_tuple_cost
@ 2005-03-16 19:45  Greg Stark <gsstark@mit.edu>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 20+ messages in thread

From: Greg Stark @ 2005-03-16 19:45 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Greg Stark <gsstark@mit.edu>; David Brown <time@bigpond.net.au>; pgsql-performance


Josh Berkus <josh@agliodbs.com> writes:

> Although I can point out that you left out the fact that the disk needs to do 
> a seek to find the beginning of the seq scan area, and even then some file 
> fragmentation is possible.   Finally, I've never seen PostgreSQL manage more 
> than 70% of the maximum read rate, and in most cases more like 30%.     

Hm. I just did a quick test. It wasn't really long enough to get a good
estimate, but it seemed to reach about 30MB/s on this drive that's only
capable of 40-50MB/s depending on the location on the platters.

That's true though, some of my calculated 25% random seeks could be caused by
fragmentation. But it seems like that would be a small part.

> > So what's going on with the empirically derived value of 4? 
> 
> It's not empirically derived; it's a value we plug into an
> internal-to-postgresql formula.

I thought Tom said he got the value by doing empirical tests.

-- 
greg




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

* Re: cpu_tuple_cost
@ 2005-03-16 20:42  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 20+ messages in thread

From: Tom Lane @ 2005-03-16 20:42 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Greg Stark <gsstark@mit.edu>; David Brown <time@bigpond.net.au>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
>> So what's going on with the empirically derived value of 4? 

> It's not empirically derived;

Yes it is.  I ran experiments back in the late 90s to derive it.
Check the archives.

Disks have gotten noticeably bigger since then, but I don't think
the ratio of seek time to rotation rate has changed much.

			regards, tom lane



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

* Re: cpu_tuple_cost
@ 2005-03-17 08:20  Manfred Koizar <mkoi-pg@aon.at>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 20+ messages in thread

From: Manfred Koizar @ 2005-03-17 08:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Sabino Mullane <greg@turnstep.com>; pgsql-performance

On Mon, 14 Mar 2005 21:23:29 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think that the "reduce random_page_cost" mantra
>is not an indication that that parameter is wrong, but that the
>cost models it feeds into need more work.

One of these areas is the cost interpolation depending on correlation.
This has been discussed on -hackes in October 2002 and August 2003
("Correlation in cost_index()").  My Postgres installations contain the
patch presented during that discussion (and another index correlation
patch), and I use *higher* values for random_page_cost (up to 10).

Servus
 Manfred



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

* Re: cpu_tuple_cost
@ 2005-03-17 09:37  Daniel Schuchardt <daniel_schuchardt@web.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 0 replies; 20+ messages in thread

From: Daniel Schuchardt @ 2005-03-17 09:37 UTC (permalink / raw)
  To: pgsql-performance

Tom Lane wrote:

> 
> Reducing random_page_cost is usually the best way to get the planner to
> favor indexscans more.
> 

Ok, I tried a bit with random_page_cost and I have set it to 1 to become 
  PG using the index on mitpln:

CIMSOFT=# ANALYSE mitpln;
ANALYZE
CIMSOFT=# SET random_page_cost=2;
SET
CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM mitpln WHERE 
date_to_yearmonth_dec(mpl_date)='20050';
                                                   QUERY PLAN

--------------------------------------------------------------------------------

  Seq Scan on mitpln  (cost=0.00..1173.78 rows=1431 width=69) (actual 
time=219.000..1125.000 rows=1266 loops=1)
    Filter: ((date_to_yearmonth_dec((mpl_date)::timestamp without time 
zone))::text = '20050'::text)
  Total runtime: 1125.000 ms
(3 rows)

CIMSOFT=# SET random_page_cost=1;
SET
CIMSOFT=# EXPLAIN ANALYSE SELECT * FROM mitpln WHERE 
date_to_yearmonth_dec(mpl_date)='20050';
                                                               QUERY PLAN

--------------------------------------------------------------------------------
  Index Scan using mitpln_yearmonth_dec on mitpln  (cost=0.00..699.01 
rows=1431 width=69) (actual time=0.000..16.000 rows=1266 loops=1)
    Index Cond: ((date_to_yearmonth_dec((mpl_date)::timestamp without 
time zone))::text = '20050'::text)
  Total runtime: 16.000 ms
(3 rows)


CIMSOFT=# \d mitpln
                                        Table "public.mitpln"
     Column    |         Type          |                         Modifiers

--------------+-----------------------+-----------------------------------------
  mpl_id       | integer               | not null default 
nextval('public.mitpln_mpl_id_seq'::text)
  mpl_date     | date                  |
  mpl_minr     | integer               | not null
  mpl_tpl_name | character varying(20) |
  mpl_feiertag | character varying(50) |
  mpl_min      | real                  |
  mpl_saldo    | real                  |
  mpl_buch     | boolean               | not null default false
  mpl_absaldo  | real                  |
  mpl_vhz      | real                  |
  dbrid        | character varying     | default nextval('db_id_seq'::text)
Indexes:
     "mitpln_pkey" PRIMARY KEY, btree (mpl_id)
     "mitpln_idindex" UNIQUE, btree (dbrid)
     "xtt5126" UNIQUE, btree (mpl_date, mpl_minr)
     "mitpln_yearmonth_dec" btree 
(date_to_yearmonth_dec(mpl_date::timestamp with
out time zone))
     "mpl_minr" btree (mpl_minr)
     "mpl_minr_nobuch" btree (mpl_minr) WHERE NOT mpl_buch


CIMSOFT=# SELECT count(*) FROM mitpln;
  count
-------
  26330
(1 row)


CREATE OR REPLACE FUNCTION date_to_yearmonth_dec(TIMESTAMP) RETURNS 
VARCHAR AS'
BEGIN
  RETURN extract(year FROM $1) || extract(month FROM $1)-1;
END'LANGUAGE plpgsql IMMUTABLE;


Daniel

PS : thats a 2.4 GHZ P4 Server with 1 GB Ram and RAID - SCSI
(WIN2000, PG8.0.1)



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

* Re: cpu_tuple_cost
@ 2005-03-17 17:54  Josh Berkus <josh@agliodbs.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 20+ messages in thread

From: Josh Berkus @ 2005-03-17 17:54 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Stark <gsstark@mit.edu>; David Brown <time@bigpond.net.au>; pgsql-performance

Tom,

> Yes it is.  I ran experiments back in the late 90s to derive it.
> Check the archives.

Hmmmm ... which list?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



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

* Re: cpu_tuple_cost
@ 2005-03-17 17:57  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 20+ messages in thread

From: Tom Lane @ 2005-03-17 17:57 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Greg Stark <gsstark@mit.edu>; David Brown <time@bigpond.net.au>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
>> Yes it is.  I ran experiments back in the late 90s to derive it.
>> Check the archives.

> Hmmmm ... which list?

-hackers, no doubt.  -performance didn't exist then.

			regards, tom lane



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

* Re: cpu_tuple_cost
@ 2005-03-17 18:26  Michael Fuhr <mike@fuhr.org>
  parent: Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 20+ messages in thread

From: Michael Fuhr @ 2005-03-17 18:26 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Greg Stark <gsstark@mit.edu>; David Brown <time@bigpond.net.au>; pgsql-performance

On Thu, Mar 17, 2005 at 09:54:29AM -0800, Josh Berkus wrote:
> 
> > Yes it is.  I ran experiments back in the late 90s to derive it.
> > Check the archives.
> 
> Hmmmm ... which list?

These look like relevant threads:

http://archives.postgresql.org/pgsql-hackers/2000-01/msg00910.php
http://archives.postgresql.org/pgsql-hackers/2000-02/msg00215.php

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/



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


end of thread, other threads:[~2005-03-17 18:26 UTC | newest]

Thread overview: 20+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-11 14:25 cpu_tuple_cost Daniel Schuchardt <daniel_schuchardt@web.de>
2005-03-11 14:27 ` Daniel Schuchardt <daniel_schuchardt@web.de>
2005-03-14 06:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-15 02:05   ` Greg Sabino Mullane <greg@turnstep.com>
2005-03-15 02:17     ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-03-15 02:23     ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-17 08:20       ` Manfred Koizar <mkoi-pg@aon.at>
2005-03-15 06:10     ` Josh Berkus <josh@agliodbs.com>
2005-03-17 09:37   ` Daniel Schuchardt <daniel_schuchardt@web.de>
2005-03-15 05:30 Re: cpu_tuple_cost Gregory Stark <gsstark@mit.edu>
2005-03-15 23:41 ` David Brown <time@bigpond.net.au>
2005-03-16 07:23   ` Greg Stark <gsstark@mit.edu>
2005-03-16 18:03     ` Josh Berkus <josh@agliodbs.com>
2005-03-16 19:45       ` Greg Stark <gsstark@mit.edu>
2005-03-16 20:42       ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-17 17:54         ` Josh Berkus <josh@agliodbs.com>
2005-03-17 17:57           ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-17 18:26           ` Michael Fuhr <mike@fuhr.org>
2005-03-16 09:42 Re: cpu_tuple_cost Magnus Hagander <mha@sollentuna.net>
2005-03-16 15:42 ` Greg Stark <gsstark@mit.edu>

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