DT
Drizzle Team•4w ago
Tenkes

Do I need to manually add indexes on foreign key columns when working with PostgreSQL?

Hello, Here's example schema: 
export const orderItems = pgTable('order_items', {
    id: serial().primaryKey(),
    quantity: smallint().notNull(),
    size: sizeEnum().notNull(),

    productId: integer()
        .notNull()
        .references(() => products.id, { onDelete: 'restrict' }),
    orderId: integer()
        .notNull()
        .references(() => orders.id, { onDelete: 'cascade' }),
    colorId: integer()
        .notNull()
        .references(() => colors.id, { onDelete: 'restrict' }),

    createdAt: timestamp().defaultNow(),
    updatedAt: timestamp()
        .defaultNow()
        .$onUpdate(() => new Date())
})
export const orderItems = pgTable('order_items', {
    id: serial().primaryKey(),
    quantity: smallint().notNull(),
    size: sizeEnum().notNull(),

    productId: integer()
        .notNull()
        .references(() => products.id, { onDelete: 'restrict' }),
    orderId: integer()
        .notNull()
        .references(() => orders.id, { onDelete: 'cascade' }),
    colorId: integer()
        .notNull()
        .references(() => colors.id, { onDelete: 'restrict' }),

    createdAt: timestamp().defaultNow(),
    updatedAt: timestamp()
        .defaultNow()
        .$onUpdate(() => new Date())
})
As you can see I have orderItems table which contains productId, orderId and colorId column, which are all foreign keys. Now my question is: Are those columns automatically indexed or do I need to manually add indexes, as shown in code below? 
export const orderItems = pgTable(
    'order_items',
    {
        id: serial().primaryKey(),
        quantity: smallint().notNull(),
        size: sizeEnum().notNull(),

        productId: integer()
            .notNull()
            .references(() => products.id, { onDelete: 'restrict' }),
        orderId: integer()
            .notNull()
            .references(() => orders.id, { onDelete: 'cascade' }),
        colorId: integer()
            .notNull()
            .references(() => colors.id, { onDelete: 'restrict' }),

        createdAt: timestamp().defaultNow(),
        updatedAt: timestamp()
            .defaultNow()
            .$onUpdate(() => new Date())
    },
// Manually adding indexes here
    t => [index().on(t.colorId), index().on(t.orderId), index().on(t.productId)] 
)
export const orderItems = pgTable(
    'order_items',
    {
        id: serial().primaryKey(),
        quantity: smallint().notNull(),
        size: sizeEnum().notNull(),

        productId: integer()
            .notNull()
            .references(() => products.id, { onDelete: 'restrict' }),
        orderId: integer()
            .notNull()
            .references(() => orders.id, { onDelete: 'cascade' }),
        colorId: integer()
            .notNull()
            .references(() => colors.id, { onDelete: 'restrict' }),

        createdAt: timestamp().defaultNow(),
        updatedAt: timestamp()
            .defaultNow()
            .$onUpdate(() => new Date())
    },
// Manually adding indexes here
    t => [index().on(t.colorId), index().on(t.orderId), index().on(t.productId)] 
)
0 Replies
No replies yetBe the first to reply to this messageJoin

Did you find this page helpful?