Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2009
    Posts
    1

    Question Unanswered: select count is too slow

    Dear all,

    I face this problem from last few days in postgres database.

    here is test2 table with only one column id
    erp_test=> \d test2
    Table "public.test2"
    Column | Type | Modifiers
    --------+---------+-----------
    id | integer |

    I insert 10,000,000 entries in this table.
    erp_test=> INSERT INTO test2 VALUES (generate_series(1,10000000));
    INSERT 0 10000000

    then i delete all the entries
    erp_test=> delete from test2;
    DELETE 10000000

    and i insert only 10 entries
    erp_test=> INSERT INTO test2 VALUES (generate_series(1,10));
    INSERT 0 10

    now i try to count no of rows in this table which take long time for this 10 rows only
    (about 2-3 second)
    erp_test=> SELECT count(*) from test2;
    count
    -------
    10
    (1 row)

    this is the output of explain analyze query of the same

    erp_test=> EXPLAIN ANALYZE SELECT count(*) from test2;
    QUERY PLAN ------------------------------------------------------------------------------------------------------------------------
    Aggregate (cost=198652.13..198652.14 rows=1 width=0) (actual time=2123.471..2123.472 rows=1 loops=1)
    -> Seq Scan on test2 (cost=0.00..169732.70 rows=11567770 width=0) (actual time=2123.454..2123.454 rows=10 loops=1)
    Total runtime: 2123.609 ms
    (3 rows)


    can any one solve my problem to speed up my select count query.

    --
    Thanks & Regards
    Kumar Anand

  2. #2
    Join Date
    Nov 2006
    Posts
    82
    Maybe try to refresh statistics

  3. #3
    Join Date
    Aug 2009
    Location
    Olympia, WA
    Posts
    337
    Short answer: Vacuum your table. (Newer versions are usually set to autovacuum.)

    Long answer: This is a well documented problem with the MVCC approach that Postgres uses. It creates 10 million rows and when you delete them it marks them as deleted but any processes that were started before the delete statement would still be able to view them all. It doesn't physically remove the dead rows until you run vacuum. So running select count will actually make it read thru 10,000,010 rows to find the 10 live ones.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    When making major changes to the numbers of records, always include a vacuum step after the record counts change. as rskl/artacus72 have mentioned, this is a well-known side effect of mvcc.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •