Skip site navigation (1) Skip section navigation (2)

stats are way off on 8.4 b1

From: Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>
To: postgres performance list <pgsql-performance(at)postgresql(dot)org>
Subject: stats are way off on 8.4 b1
Date: 2009-04-17 23:12:49
Message-ID: 2f4958ff0904171612u3d4fff1fhd7ba2bef2445454a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
crawler=# select *  from assigments;
 jobid | timeout | workerid
-------+---------+----------
(0 rows)

Time: 0.705 ms
crawler=# \d+ assigments
                                           Table "public.assigments"
  Column  |           Type           |                    Modifiers
                | Storage | Description
----------+--------------------------+-------------------------------------------------+---------+-------------
 jobid    | bigint                   | not null
                | plain   |
 timeout  | timestamp with time zone | not null default (now() +
'00:02:00'::interval) | plain   |
 workerid | bigint                   | not null
                | plain   |
Indexes:
    "assigments_pkey" PRIMARY KEY, btree (jobid)
Foreign-key constraints:
    "assigments_jobid_fkey" FOREIGN KEY (jobid) REFERENCES jobs(id)
MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no

crawler=# \d+ jobs
                                                Table "public.jobs"
   Column   |           Type           |                     Modifiers
                    | Storage | Description
------------+--------------------------+---------------------------------------------------+---------+-------------
 id         | bigint                   | not null default
nextval('jobs_id_seq'::regclass) | plain   |
 domainid   | bigint                   | not null
                    | plain   |
 priority   | smallint                 | not null default 1
                    | plain   |
 added      | timestamp with time zone | not null default now()
                    | plain   |
 notify_end | boolean                  | not null default false
                    | plain   |
Indexes:
    "jobs_pkey" PRIMARY KEY, btree (domainid)
    "job_id_uidx" UNIQUE, btree (id)
    "foo" btree (notify_end DESC, priority DESC, added)
    "foo_bar" btree (notify_end, priority, added)
    "jobs_worker_priority_on_jobs" btree (calc_prio(notify_end,
priority, added))
Foreign-key constraints:
    "jobs_domain_id_fkey" FOREIGN KEY (domainid) REFERENCES
domains(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE DEFERRABLE
Referenced by:
  "assigments_jobid_fkey" IN assigments FOREIGN KEY (jobid) REFERENCES
jobs(id) MATCH FULL ON UPDATE CASCADE ON DELETE CASCADE
Has OIDs: no

crawler=# explain analyze select * from full_assigments_view;
                                                      QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..11040.77 rows=1510 width=31) (actual
time=0.003..0.003 rows=0 loops=1)
   ->  Nested Loop  (cost=0.00..10410.97 rows=1510 width=24) (actual
time=0.002..0.002 rows=0 loops=1)
         ->  Seq Scan on assigments a  (cost=0.00..25.10 rows=1510
width=16) (actual time=0.002..0.002 rows=0 loops=1)
         ->  Index Scan using job_id_uidx on jobs j  (cost=0.00..6.87
rows=1 width=16) (never executed)
               Index Cond: (j.id = a.jobid)
   ->  Index Scan using domains_id_idx on domains d  (cost=0.00..0.40
rows=1 width=19) (never executed)
         Index Cond: (d.id = j.domainid)
 Total runtime: 0.123 ms
(8 rows)

Time: 1.390 ms

             View "public.full_assigments_view"
   Column    |  Type   | Modifiers | Storage  | Description
-------------+---------+-----------+----------+-------------
 domain_name | text    |           | extended |
 job_id      | bigint  |           | plain    |
 timed_out   | boolean |           | plain    |
View definition:
 SELECT d.name AS domain_name, j.id AS job_id, (now() - a.timeout) >
'00:00:00'::interval AS timed_out
   FROM assigments a
   JOIN jobs j ON a.jobid = j.id
   JOIN domains d ON d.id = j.domainid;


default_statistics_target=100
all the other settings are pretty much default,

That expected 1510 rows in 'assigments' seems to be pretty off,
especially since I just vacuumed/analyze the db.
Any ideas ?


-- 
GJ

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2009-04-17 23:29:57
Subject: Re: stats are way off on 8.4 b1
Previous:From: Matthew WakelingDate: 2009-04-17 17:18:45
Subject: Re: GiST index performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group