Index scan vs table inheritance - Mailing list pgsql-general

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.