Search Postgresql Archives

Index scan vs table inheritance

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

 



Title: Index scan vs table inheritance

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.


[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