Hi All,
Now I have PostgreSQL 8.3.4 and next problem:
I have hierarchy of tables:
Master table (empty, has not data, indexes and over). Generally it is empty, but in production it may have some data or indexes and I have to select from it for backward compatibility.
Child tables inherited from data and have ‘time’ field indexed. There are about 1 million real rows there.
During Execution of query:
SELECT * FROM master ORDER BY time LIMIT 100
is see sequence scan both master and child tables:
EXPLAIN SELECT * FROM master
ORDER BY time LIMIT 100;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Limit (cost=76715.88..76716.13 rows=100 width=1374)
-> Sort (cost=76715.88..79511.69 rows=1118326 width=1374)
Sort Key: public.master.time
-> Result (cost=0.00..33974.26 rows=1118326 width=1374)
-> Append (cost=0.00..33974.26 rows=1118326 width=1374)
-> Seq Scan on master (cost=0.00..10.50 rows=50 width=1374)
* -> Seq Scan on child master (cost=0.00..33963.76 rows=1118276 width=1374) *
But if I direct:
SELECT * FROM child ORDER BY time LIMIT 100
or use UNION clause:
((SELECT * FROM ONLY master ORDER BY time LIMIT 100
UNION ALL
SELECT * FROM child ORDER BY time LIMIT 100 ) )
ORDER BY LIMIT 100;
I see index scan on child as expected and sequence scan on Master (OK there is no any data).
# EXPLAIN ((SELECT * FROM ONLY master ORDER BY time LIMIT 100 )
UNION ALL
( SELECT * FROM child ORDER BY time LIMIT 100 ) )
ORDER BY time LIMIT 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=170.42..170.67 rows=100 width=1374)
-> Sort (cost=170.42..170.80 rows=150 width=1374)
Sort Key: master.time
-> Append (cost=11.91..165.00 rows=150 width=1374)
-> Limit (cost=11.91..12.04 rows=50 width=1374)
-> Sort (cost=11.91..12.04 rows=50 width=1374)
Sort Key: master.time
-> Seq Scan on master (cost=0.00..10.50 rows=50 width=1374)
-> Limit (cost=0.00..151.47 rows=100 width=1374)
* -> Index Scan using child_time_index on child (cost=0.00..1693818.51 rows=1118276 width=1374) *
The question is: Why index scans is not used on general query to Master?
I tried VACUUM ANALYZE and REINDEX ON Both tables, created index on ‘time’ in ‘master’ table but nothing changed.
Is it some knows issue and I have to migrate up to 9.0.3 or it is some statistic misusage by planner and somehow can be reconfigured?
Thanks in advance,
-------------------------------------
BR,
Artem Shpynov aka FYR.
