Search Postgresql Archives

Re: more problems with count(*) on large table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



"Alban Hertroys" <a.hertroys@xxxxxxxxxxxxxxxxx> writes:

> Mike Charnoky wrote:
>> With respect to the ALTER TABLE SET STATISTICS... how do I determine a
>> good value to use?  This wasn't really clear in the pg docs.  Also, do I
>> need to run ANALYZE on the table after I change the statistics?
>> 
>> Here are the EXPLAINs from the queries:
>> 
>> db=# explain select count(*) from prediction_accuracy where evtime
>> between '2007-09-25' and '2007-09-26';
>> 
>> QUERY PLAN
>> 
>> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>>  Aggregate  (cost=475677.40..475677.41 rows=1 width=0)
>>    ->  Index Scan using pred_acc_evtime_index on prediction_accuracy
>> (cost=0.00..444451.44 rows=12490383 width=0)
>>          Index Cond: ((evtime >= '2007-09-25 00:00:00-07'::timestamp
>> with time zone) AND (evtime <= '2007-09-26 00:00:00-07'::timestamp with
>> time zone))
>> (3 rows)
>> 
>> db=# explain select count(*) from prediction_accuracy where evtime
>> between '2007-09-26' and '2007-09-27';
>> 
>> QUERY PLAN
>> 
>> ---------------------------------------------------------------------------------------------------------------------------------------------------------
>>  Aggregate  (cost=486615.04..486615.05 rows=1 width=0)
>>    ->  Index Scan using pred_acc_evtime_index on prediction_accuracy
>> (cost=0.00..454671.07 rows=12777586 width=0)
>>          Index Cond: ((evtime >= '2007-09-26 00:00:00-07'::timestamp
>> with time zone) AND (evtime <= '2007-09-27 00:00:00-07'::timestamp with
>> time zone))
>> (3 rows)
>
> Interesting, same plans and no sequential scans... Yet totally different
> run times. Almost as if something prevents you to read some records
> between 26 and 27 september...

Just to be sure we're looking at the right plan do this:

\timing
PREPARE stmt AS 
 SELECT count(*) 
   FROM prediction_accuracy 
  WHERE evtime BETWEEN '2007-09-25' AND '2007-09-26';

EXPLAIN EXECUTE stmt;
EXECUTE stmt;

> I'm no expert on locking in Postgres, but AFAIK locks that prevent you
> from reading records are rather rare and probably only issued from
> userland code.

Pages can be locked but never for very long.

What other work is going on in this server? Is there anything which might be
locking the table periodically (either a VACUUM FULL, CLUSTER, ALTER TABLE,
etc?) 

Perhaps there's a hardware problem, is there anything in your system logs from
dmesg?

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux