On Mon, Apr 7, 2008 at 2:05 AM, Alban Hertroys <[EMAIL PROTECTED]> wrote: > On Apr 7, 2008, at 1:32 AM, David Wilson wrote: > > > > > The databases estimates seem consistent with yours, so why is it doing > this? Could you provide an EXPLAIN ANALYSE? It shows the actual numbers next > to the estimates, although I figure that query might take a while...
explain analyze select distinct datestamp from vals; QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------- Unique (cost=15003047.47..15380004.83 rows=4263 width=4) (actual time=649599.159..721671.595 rows=4252 loops=1) -> Sort (cost=15003047.47..15191526.15 rows=75391472 width=4) (actual time=649599.157..694392.602 rows=75391476 loops=1) Sort Key: datestamp Sort Method: external merge Disk: 1178592kB -> Seq Scan on vals (cost=0.00..1531261.72 rows=75391472 width=4) (actual time=9.104..93130.468 rows=75391476 loops=1) Total runtime: 722379.434 ms (There were a couple other very long-running, disk-intensive queries going on in the background of this, so that runtime is a little inflated, but the values should still all be relevant.) > Pg estimates the costs quite high too. It's almost as if there isn't an > index on that column and it has no other way then doing a sequential scan... > Could you show us the table definition and its indexes? What version of Pg > is this? Pg is 8.3.1 Table definition: CREATE TABLE vals ( sid integer NOT NULL, eid integer NOT NULL, datestamp integer NOT NULL, val_dur integer NOT NULL, acc real NOT NULL, yld real NOT NULL, rt real NOT NULL, ydev real NOT NULL, vydev real NOT NULL, adev real NOT NULL, achange real NOT NULL, ychange real NOT NULL, arsi real NOT NULL, yrsi real NOT NULL, UNIQUE (sid,eid,val_dur,datestamp), FOREIGN KEY (sid,eid,datestamp) REFERENCES preds (sid,eid,datestamp) ON DELETE CASCADE ); create index val_datestamp_idx on vals(datestamp); create index val_evaluator_idx on vals(eid); create index val_search_key on vals(val_dur,eid,datestamp); create index val_vd_idx on vals(val_dur,datestamp); (The various indices are for a variety of common queries into the table) > It may be that your index on vals.datestamp doesn't fit into memory; what > are the relevant configuration parameters for your database? That's a very good question. I recently had to rebuild this particular database and haven't played with the configuration parameters as much as I'd like- what parameters would be most relevant here? I hadn't realized that an index needed to fit into memory. pg_total_relation_size('vals') - pg_relation_size('vals') gives 11gb. All indexed columns are integers. My guess is that this means that it's likely the index doesn't fit into memory. -- - David T. Wilson Princeton Satellite Systems [EMAIL PROTECTED] -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general