I have a very large table. So I partitioned this table. And copied all records from master table to child tables. Then I deleted all records from master table. Now my master table is empty. It is very fast to do query on child table. But it is still very slow to do query on master table. What's wrong with it?
postgres=# select count(*) from only cdr;
count
-------
0
(1 row)
postgres=# explain select count(*) from only cdr;
QUERY PLAN
-----------------------------------------------------------------------
Aggregate (cost=2045094.31..2045094.32 rows=1 width=0)
-> Seq Scan on cdr (cost=0.00..2044867.85 rows=90585 width=0)
(2 rows)
postgres=# EXPLAIN ANALYZE select count(*) from only cdr;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
-----
Aggregate (cost=2045094.31..2045094.32 rows=1 width=0) (actual time=168385.356..168385.356 rows=1 loops=1)
-> Seq Scan on cdr (cost=0.00..2044867.85 rows=90585 width=0) (actual time=168385.351..168385.351 rows=0 loop
s=1)
Total runtime: 168385.404 ms
(3 rows)
I also ran vacuum analyze on master table. But still no luck.
postgres=# vacuum analyze cdr; VACUUM
I found some other people also got same issue. Maybe the root cause is that though the old records in master table have been deleted, psql somehow still scan these garbage data. One solution is to create a new master table, and abandon the old master table. But this solution is not suitable for me. Any other solution without downtime? Thanks!
