Postgresql index best pratices / performance

Question

I have a test Postgresql on 10.13 and looking for several answers on indexes. Let's take this table for example and admit there are 40k entries in it :

CREATE TABLE "public"."ecom_input"
(
    "id"            serial PRIMARY KEY,
    "ecom_id"       INTEGER NOT NULL,
    "sku_supplier"  CHARACTER VARYING(100) NOT NULL
);

Does a PRIMARY KEY have its own INDEX automatically? With this query :

EXPLAIN SELECT * FROM ecom_input WHERE id = 27846;

I have the same results whether using the PRIMARY KEY or the INDEX :

--> Index Scan using ecom_input_pkey on ecom_input  (cost=0.29..8.31 rows=1 width=853)      
--> Index Scan using "ecom_input_id_idx" on ecom_input  (cost=0.29..8.31 rows=1 width=853)

After creating a multi column INDEX on the same table :

CREATE INDEX idx_ecom_input ON ecom_input (ecom_id, sku_supplier);

Try :

EXPLAIN SELECT * FROM ecom_input WHERE ecom_id = 22 AND sku_supplier = 'MATHILDEJAS';

I have good cost performance:

--> Index Scan using idx_ecom_input on ecom_input  (cost=0.41..8.43 rows=1 width=853)

Loosing a bit of performance when using only WHERE sku_supplier = 'MATHILDEJAS'; :

--> Index Scan using idx_ecom_input on ecom_input  (cost=0.41..1044.64 rows=1 width=853)

But when using only WHERE ecom_id = 22; :

--> Bitmap Heap Scan on ecom_input  (cost=5.58..547.25 rows=150 width=853)
      Recheck Cond: (ecom_id = 22)
        ->  Bitmap Index Scan on idx_ecom_input  (cost=0.00..5.54 rows=150 width=0)
                Index Cond: (ecom_id = 22)

Why does the optimizer uses a Bitmap Heap Scan when using only the ecom_id in the WHERE clause but not when using the sku_supplier in the WHERE clause ? There are 150 rows where ecom_id = 22 but & only 1 row where sku_supplier = 'MATHILDEJAS', is this the reason ?

We usually operate JOIN on the column 'id' of our tables (which are PRIMARY KEYS). Is an INDEX used during JOIN operations ? If yes, is it a good pratice to always JOIN on a PRIMARY KEY ?
At what point (approximately) can we see a real performance difference when querying using INDEX and when NOT ? We are using cloudbased databases and even if the network is good, the round trip server-client through internet is what is taking longer.

Exemple :

EXPLAIN ANALYZE SELECT * FROM ecom_input WHERE sku_supplier = 'MATHILDEJAS' AND ecom_id = 22;

With INDEX :

"Index Scan using idx_ecom_input on ecom_input  (cost=0.41..8.43 rows=1 width=853) (actual time=0.017..0.018 rows=1 loops=1)"
"Index Cond: ((ecom_id = 22) AND ((sku_supplier)::text = 'MATHILDEJAS'::text))"
"Planning time: 0.086 ms"
"Execution time: 0.034 ms"

Without INDEX :

"Seq Scan on ecom_input  (cost=0.00..10034.43 rows=1 width=853) (actual time=0.006..13.555 rows=1 loops=1)"
"Filter: (((sku_supplier)::text = 'MATHILDEJAS'::text) AND (ecom_id = 22))"
"Rows Removed by Filter: 40561"
"Planning time: 0.097 ms"
"Execution time: 13.572 ms"

We gain 13ms in total. This requests take up to 250-700ms to go to the server and back. It is not really impacting on a 40k entries table. Do you know about how many entries and INDEX would be useful ?

Postgresql index best pratices / performance

0 Answers