0

how to set the right index(es) for this schema ?

I have a product table where I show at first active products like this:

SELECT name, price, categoryid FROM products WHERE status = 1;

Then a people can filter like price then I do this:

SELECT name, price FROM products WHERE price > 100 AND status = 1;

But another want to filter like price and category then I do this:

SELECT name, price, categoryid FROM products WHERE price > 100 AND categoryid = 4 AND status = 1

Now how can I set right index and noncluster/filtered index ?

At now I have done this:

CREATE INDEX I_PRODUCT_ACTIVE_NONC ON product(categoryid) WHERE status = 1;

Then I have think about this when he want to filter both:

CREATE INDEX I_PRODUCT_ACTIVE_NONC_FILTER ON product(price, categoryid) WHERE status = 1;

But when he want to filter only price then this is effective:

CREATE INDEX I_PRODUCT_ACTIVE_NONC_FILTER ON product(price) WHERE status = 1;

So how many index should I create for this example and it is okey to have many filtered indexes or it is bad ? because I have ohter columns like color and size, this has be indexed too.

Any expert can help me what I should have to do ?

newstacker
  • 59
  • 5
  • Create a representative dataset and start testing, using explain (analyze, verbose, buffers) for your SQL statements. The database can tell you what’s best, just ask – Frank Heikens May 30 '23 at 17:58

2 Answers2

0

Quickly said, the clustered index should always be on the primary key (PK). For the rest, it depends on your volume and performance. Just remember that the more diverse the values, the more efficient an index can be.

iDevlop
  • 24,841
  • 11
  • 90
  • 149
  • So more nonclustered indexes would not impact the search performance doenst matter when I have 8 nonclsutered indexes ? (Also when it set it right) – newstacker May 30 '23 at 16:15
  • PostgreSQL doesn't really have a notion of "Clustered index" (in the same way as SQL Server, for example). See https://stackoverflow.com/a/47671252/372643 – Bruno May 30 '23 at 19:18
0

With PostgreSQL, you can create and use multi-column indexes. The order generally matters, and you'll generally want the column on which there is a equality comparion before those that have an inequality comparison (and before those that are unused).

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned.

For a query like this:

SELECT name, price, categoryid FROM products
    WHERE price > 100 AND categoryid = 4 AND status = 1

An index products(status, categoryid, price) will work better than an index on product(status, price, categoryid).

Or, if you're always using status=1, you can use CREATE INDEX ON products(categoryid, price) WHERE status=1

For this query, the index on products(categoryid, price) WHERE status=1 is unlikely to be used:

SELECT name, price FROM products
WHERE price > 100 AND status = 1

In this case, it's probably worth having another index on products(price) WHERE status=1 (or on products(status, price)).

Remember to run ANALYZE after creating the index so that the planner can make better estimates before choosing which indexes to use.

You can see what's used using EXPLAIN ANALYZE. This should display what's used.

EXPLAIN ANALYZE
    SELECT name, price, categoryid FROM products
    WHERE price > 100 AND categoryid = 4 AND status = 1

If you create a number of indexes, it's worth checking their usage statistics. (Don't necessarily create too many useless indexes, since it may confuse the planner.)

Bruno
  • 119,590
  • 31
  • 270
  • 376