Poor performance in a SELECT using Inheritance

Question

I will detail my problem in good detail. I have a marketplace application so I make a decision to make a multitenant per schema approach. I like this approach because of the isolation of data, but I'm facing so many problems that I want to get out of this.

In the first days it is good to go but today I have 3500 schemas and is really hard to make maintenance. I make a famous approach with schemas with a master table that use Inheritance on all my child ( tenants ) tables, but not all, just a few.

So when I execute this simple select in a master.table

SELECT bra_f.file_id, bra_f.file_path
       FROM master.bra_file bra_f
       WHERE bra_f.product_id = :productId
       AND bra_f.active = true

It takes to me 7 seconds to perform this simple query with a GLOBAL SEQUENCE from product_id that I put on all child bra_product tables.

My problem is similar to this: https://stackoverflow.com/questions/62013115/improve-postgresql-select-performance-in-a-parent-table-inheritance/62017449#62017449

I really thinking seriously to put all my data in shared tables because and change all my project. I am really sad about my decision to make a multi-tenant schema approach.

What I must do to make this performance good? and don't lose my precious time to refactor my project.

This is my explanation, I can't perform exactly in the database because I make a loop on each product_id on my laravel backend and I can't use this in Postgres.

Example this is my loop:

public function getMasterProdFullTeste($productList)
{
    $size = count($productList);
    for ($i = 0; $i < $size; $i++) {
        $productList[$i]->srcimglist = $this->mdFile->getMasterImageByProductTeste($productList[$i]);
    }
    dd($productList);
    return $productList;
}

And this is my function that executes on my model:

 public function getMasterImageByProductTeste($product)
    {

       return DB::select('SELECT bra_f.file_id, bra_f.file_path
                            FROM master.bra_file bra_f
                            WHERE bra_f.product_id = :productId
                            AND bra_f.active = true', ['productId' => $product->product_id]);

}

Example this is the query that execute in each loop:

   SELECT bra_f.file_id, bra_f.file_path
           FROM master.bra_file bra_f
           WHERE bra_f.product_id = :productId
           AND bra_f.active = true;

If I use EXPLAIN it gives me this:

Append  (cost=0.00..11593.26 rows=1675 width=463)
  ->  Seq Scan on bra_file bra_f  (cost=0.00..0.00 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_1  (cost=0.00..1.02 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_2  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_3  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_4  (cost=0.00..1.02 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_5  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_6  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_7  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_8  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_9  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_10  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_11  (cost=0.00..1.16 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_12  (cost=0.00..3.55 rows=1 width=58)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_13  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_14  (cost=0.00..4.49 rows=1 width=56)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_15  (cost=0.00..1.19 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_16  (cost=0.00..2.58 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_17  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_18  (cost=0.00..2.11 rows=1 width=59)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_19  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_20  (cost=0.00..1.18 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_21  (cost=0.00..1.24 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_22  (cost=0.00..2.62 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_23  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_24  (cost=0.00..1.04 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_25  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_26  (cost=0.00..1.04 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_27  (cost=0.00..1.19 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_28  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_29  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_30  (cost=0.00..3.74 rows=1 width=39)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_31  (cost=0.00..2.42 rows=1 width=70)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_32  (cost=0.00..1.05 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_33  (cost=0.00..1.20 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_34  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_35  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_36  (cost=0.00..3.46 rows=1 width=70)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_37  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_38  (cost=0.00..1.05 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_39  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_40  (cost=0.00..1.07 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_41  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_42  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_43  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_44  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_45  (cost=0.00..1.15 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_46  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_47  (cost=0.00..1.10 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_48  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_49  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_50  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_51  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_52  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_53  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_54  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_55  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_56  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_57  (cost=0.00..1.20 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_58  (cost=0.00..1.02 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_59  (cost=0.00..1.11 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_60  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_61  (cost=0.00..3.58 rows=1 width=70)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_62  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_63  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_64  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_65  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_66  (cost=0.00..2.38 rows=1 width=70)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_67  (cost=0.00..1.10 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_68  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_69  (cost=0.00..1.16 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_70  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_71  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_72  (cost=0.00..1.16 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_73  (cost=0.00..2.40 rows=1 width=70)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_74  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_75  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_76  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_77  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_78  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_79  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_80  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_81  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_82  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_83  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_84  (cost=0.00..1.15 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_85  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_86  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_87  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_88  (cost=0.00..1.12 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_89  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_90  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_91  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_92  (cost=0.00..1.15 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_93  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_94  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_95  (cost=0.00..3.46 rows=1 width=70)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_96  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_97  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_98  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_99  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_100  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_101  (cost=0.00..1.11 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_102  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_103  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_104  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_105  (cost=0.00..1.21 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_106  (cost=0.00..1.14 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_107  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_108  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_109  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_110  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_111  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_112  (cost=0.00..1.04 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_113  (cost=0.00..1.05 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_114  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_115  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_116  (cost=0.00..1.19 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_117  (cost=0.00..1.05 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_118  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_119  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_120  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_121  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_122  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_123  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_124  (cost=0.00..1.23 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_125  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_126  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_127  (cost=0.00..1.25 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_128  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_129  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_130  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_131  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_132  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_133  (cost=0.00..2.21 rows=1 width=40)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_134  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_135  (cost=0.00..1.31 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_136  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_137  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_138  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_139  (cost=0.00..1.05 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_140  (cost=0.00..1.19 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_141  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_142  (cost=0.00..3.69 rows=1 width=39)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_143  (cost=0.00..2.40 rows=1 width=39)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_144  (cost=0.00..3.25 rows=1 width=49)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_145  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_146  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_147  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_148  (cost=0.00..1.20 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_149  (cost=0.00..1.06 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_150  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_151  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_152  (cost=0.00..1.05 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_153  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_154  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_155  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_156  (cost=0.00..1.19 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_157  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_158  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_159  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_160  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_161  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_162  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_163  (cost=0.00..6.38 rows=1 width=54)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_164  (cost=0.00..1.07 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_165  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_166  (cost=0.00..7.34 rows=1 width=50)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_167  (cost=0.00..5.22 rows=1 width=69)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_168  (cost=0.00..1.01 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_169  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_170  (cost=0.00..1.05 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_171  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_172  (cost=0.00..1.12 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_173  (cost=0.00..1.07 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_174  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_175  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_176  (cost=0.00..2.34 rows=1 width=39)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_177  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_178  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_179  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_180  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_181  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_182  (cost=0.00..1.10 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_183  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_184  (cost=0.00..10.88 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_185  (cost=0.00..1.09 rows=1 width=524)
        Filter: (active AND (product_id = 6459))
  ->  Seq Scan on bra_file bra_f_186  (cost=0.00..10.88 rows=1 width=524)

In my database only take me 742ms but in my PHP 1.7 seconds

@CL. thank you for the response, indexes in my child table? If you ask about constant they all have for the primary key, the problem is that master schema is an INHERITANCE table, so he doesn't have any data like Laurenz say in the link that I posted. The query won't use the index on MasterAccount.imovel because that table itself contains no data, so it makes no sense to use the index. — wavrik, Commented Jul 21, 2020 at 14:02
It makes sense to use indexes on the child tables. But they need to exist first. Is the primary key you refer to on product_id, or something else? — jjanes, Commented Jul 21, 2020 at 14:08
My child table exists, I create a table child and after I created it I make an Inheritance to the master. My primary key is product_id that I created with a sequence from the master. master.bra_product has a primary key and has a sequence, so I make a global sequence for product_id on all children. — wavrik, Commented Jul 21, 2020 at 14:11

Stack Exchange Network

Poor performance in a SELECT using Inheritance

0

Your Answer

Browse other questions tagged
postgresql
postgresql-9.5
inheritance
or ask your own question.

Hot Network Questions

Poor performance in a SELECT using Inheritance

0

Know someone who can answer? Share a link to this question via email, Twitter, or Facebook.

Your Answer

Sign up or log in

Post as a guest

Browse other questions tagged postgresqlpostgresql-9.5inheritance or ask your own question.

Related

Hot Network Questions

Browse other questions tagged
postgresql
postgresql-9.5
inheritance
or ask your own question.