Thread: indexes on partitioned tables

Hi All,

I have partitioned a table and levied indexes on it but when I do maintenance activities like reindexing it's not happening and the error is index not present on the partitioned table.

Please highlight some points on it, Is I am missing something?

Hi Ron 

I created the indexes on the parent table globally and ran the create script with partitioned tables script, the parent table with child tables are created and I can see indexes are created on child tables too.

Then I inserted data from existing table.

Thanks,

Wasim

On Thu, 11 Jul, 2024, 6:09 pm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

On Thu, Jul 11, 2024 at 4:16 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi All,

I have partitioned a table and levied indexes on it but when I do maintenance activities like reindexing it's not happening and the error is index not present on the partitioned table.

Please highlight some points on it, Is I am missing something?

Show us the code.  Maybe you're doing "CREATE INDEX ... ON ONLY ...;"   Or something else; we don't know, since your question is light on details.

Hi Ron 

I created the indexes on the parent table globally and ran the create script with partitioned tables script, the parent table with child tables are created and I can see indexes are created on child tables too.

Then I inserted data from existing table.

Thanks,

Wasim

On Thu, 11 Jul, 2024, 6:09 pm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

On Thu, Jul 11, 2024 at 4:16 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi All,

I have partitioned a table and levied indexes on it but when I do maintenance activities like reindexing it's not happening and the error is index not present on the partitioned table.

Please highlight some points on it, Is I am missing something?

Show us the code.  Maybe you're doing "CREATE INDEX ... ON ONLY ...;"   Or something else; we don't know, since your question is light on details.

"and I can see indexes are created on child tables too."

That's what's supposed to happen.

As for errors... you haven't shown us any actual errors, only vague descriptions.

On Fri, Jul 12, 2024 at 1:31 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi Ron 

I created the indexes on the parent table globally and ran the create script with partitioned tables script, the parent table with child tables are created and I can see indexes are created on child tables too.

Then I inserted data from existing table.

Thanks,

Wasim

On Thu, 11 Jul, 2024, 6:09 pm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

On Thu, Jul 11, 2024 at 4:16 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi All,

I have partitioned a table and levied indexes on it but when I do maintenance activities like reindexing it's not happening and the error is index not present on the partitioned table.

Please highlight some points on it, Is I am missing something?

Show us the code.  Maybe you're doing "CREATE INDEX ... ON ONLY ...;"   Or something else; we don't know, since your question is light on details.

Sorry there is no error it's a pgadmin that says no index is on the partition table for reindexing.

On Sat, 13 Jul, 2024, 1:11 am Zaid Shabbir, <zaidshabbir@gmail.com> wrote:

Hello Ron,

Can you plz provide some details about your scenario like

Platform, PostgreSQL version, table details and queries you use.

Above information helps to identify the scenario and possible solution.

Thanks 

On Sat, 13 Jul 2024 at 12:36 AM, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

"and I can see indexes are created on child tables too."

That's what's supposed to happen.

As for errors... you haven't shown us any actual errors, only vague descriptions.

On Fri, Jul 12, 2024 at 1:31 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi Ron 

I created the indexes on the parent table globally and ran the create script with partitioned tables script, the parent table with child tables are created and I can see indexes are created on child tables too.

Then I inserted data from existing table.

Thanks,

Wasim

On Thu, 11 Jul, 2024, 6:09 pm Ron Johnson, <ronljohnsonjr@gmail.com> wrote:

On Thu, Jul 11, 2024 at 4:16 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi All,

I have partitioned a table and levied indexes on it but when I do maintenance activities like reindexing it's not happening and the error is index not present on the partitioned table.

Please highlight some points on it, Is I am missing something?

Show us the code.  Maybe you're doing "CREATE INDEX ... ON ONLY ...;"   Or something else; we don't know, since your question is light on details.

Hi Wasim

On Fri, Jul 12, 2024 at 10:23 AM Wasim Devale <wasimd60@gmail.com> wrote:

Hi Kashif

Is there a steps to create the the partition tables ? 

Yes, you can check the following link

https://www.postgresql.org/docs/current/ddl-partitioning.html 

I mean to say first create the tables and then insert the data lastly create index on them ?

It's up to you which way you follow

You can create the table and indexes first and insert data later and vice versa.

What I did I created the table at once with all partition tables and indexes and then I inserted data by insert script. Is it ok? 

Yes 

And I also created the indexes globally on the parent table the partition tables automatically took the indexes. Let me know what you think.

Thanks,

Wasim

On Thu, 11 Jul, 2024, 1:54 pm Kashif Zeeshan, <kashi.zeeshan@gmail.com> wrote:

Hi

When you create an index on Partition table then the index is created for all the Partitions so when you re-index then you need to re-index each partition separately.

Regards

Kashif Zeeshan

On Thu, Jul 11, 2024 at 1:16 PM Wasim Devale <wasimd60@gmail.com> wrote:

Hi All,

I have partitioned a table and levied indexes on it but when I do maintenance activities like reindexing it's not happening and the error is index not present on the partitioned table.

Please highlight some points on it, Is I am missing something?

Thanks,

Wasim