Strange query plan - Mailing list pgsql-performance

Dear all,

I am new to PG but I have a solid background on tuning in Oracle and MSSQL.

I have a query coming out from a piece of software from our SW-Stack (I can’t change it) and of course it takes a large amount of time.

The table I am query are inherited (partitioned) and the query is the following (names are changed for policy):

[SELECT]LOG:  duration: 427514.480 ms  execute <unnamed>: select

            "f_table"."fk_column" as "c0",

            "f_table"."fk_column" as "c1"

        from

            "s_schema"."f_table" as "f_table"

       where

            "f_table"."fk_column" = 'somevalue'

        group by

            "f_table"."fk_column"

        order by

            "f_table"."fk_column" ASC

the fk_column/somevalue is the “partition key” and the planner correctly purge the inherited table accordingly.

Records in partitions vary from a min of 30M to max of 160M rows.

'Group  (cost=0.00..4674965.80 rows=200 width=17)'

'  ->  Append  (cost=0.00..4360975.94 rows=125595945 width=17)'

'        ->  Index Scan using f_table_pkey on f_table  (cost=0.00..5.64 rows=1 width=58)'

'              Index Cond: ((fk_column)::text = 'somevalue'::text)'

'        ->  Seq Scan on f_table _scxc f_table  (cost=0.00..4360970.30 rows=125595944 width=17)'

'              Filter: ((fk_column)::text = 'somevalue'::text)'

disabling the seq_scan do not help it forces the index but it takes ages.

In each partition the value of fk_column is just one (being the partition key) and I am expecting that this is checked on the constraint by the planner.

Furthermore I have put an index on fk_column (tried both btree and hash) however the plan is always a seq_scan on the partition, even if the index has only one value?

Regardless the constraint (which I think it should be taken into consideration here) I am expecting that through Index Scan would easily figure out that the value.

In theory there should be no need to access the table here but perform everything on the index object (and of course in the “father” table).

Furthemore I don’t understand why on the main table is using an index scan (on 0 rows).

Yes: Analyzed.

I fear I am missing something on Index usage in Postgres.

cheers,

g

4 CPU (on VMWare) + 8G of RAM

seq_page_cost = 1.0                     # measured on an arbitrary scale

random_page_cost = 2.5                  # same scale as above

cpu_tuple_cost = 0.01                   # same scale as above

cpu_index_tuple_cost = 0.005            # same scale as above

cpu_operator_cost = 0.0025              # same scale as above

default_statistics_target = 100 # range 1-10000

constraint_exclusion = partition        # on, off, or partition

shared_buffers = 960MB                  # min 128kB

temp_buffers = 256MB                    # min 800kB

max_prepared_transactions = 100         # zero disables the feature

work_mem = 192MB                                # min 64kB

maintenance_work_mem = 480MB            # min 1MB