pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Jim C. Nasby <jim@nasby.net>
To: pgsql-performance@postgresql.org
Subject: More tablescanning fun
Date: Thu, 24 Apr 2003 18:38:17 -0500
Message-ID: <20030424183817.A66185@flake.decibel.org> (raw)

On this table

 project_id | integer | not null
 id         | integer | not null
 date       | date    | not null
 team_id    | integer | not null
 work_units | bigint  | not null
Indexes: email_contrib_pkey primary key btree (project_id, id, date)

with this breakdown of data

 project_id |  count   
------------+----------
          5 | 56427141
          8 |  1058843
         24 |   361595
         25 |  4092575
        205 | 58512516

Any kind of operation on an entire project wants to tablescan, even
though it's going to take way longer.

explain analyze select sum(work_units) from email_contrib where
project_id=8;

Index scan  126, 56, 55 seconds
Seq. scan   1517, 850, 897 seconds 

It seems like the metrics used for the cost of index scanning v. table
scanning on large tables need to be revisited. It might be such a huge
difference in this case because the table is essentially clustered on
the primary key. I can test this by doing an aggregate for, say, a
specific team_id, which would be pretty well spread across the entire
table, but that'll have to wait a bit.

Anyone have any thoughts on this? Also, is there a TODO to impliment
real clustered indexes? Doing stuff by project_id on this table in
sybase was very efficient, because there was a real clustered index on
the PK. By clustered index, I mean an index where the leaf nodes of the
B-tree were the actual table rows. This means the only overhead in going
through the index is scanning the branches, which in this case would be
pretty light-weight.

Is this something that I should be using some PGSQL-specific feature
for, like inheritance?

I've been really happy so far with PGSQL (comming from Sybase and DB2),
but it seems there's still some pretty big performance issues that want
to be addressed (or I should say performance issues that hurt really big
when you hit them :) ).
-- 
Jim C. Nasby (aka Decibel!)                    jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"




view thread (11+ messages)  latest in thread

Message-ID: <20030424183817.A66185@flake.decibel.org>
Permalink:  ../20030424183817.A66185@flake.decibel.org/
Also on:    postgresql.org/message-id/20030424183817.A66185@flake.decibel.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: jim@nasby.net
  Subject: Re: More tablescanning fun
  In-Reply-To: <20030424183817.A66185@flake.decibel.org>
  References: <20030424183817.A66185@flake.decibel.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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