Comment by ansc
3 years ago
>There was a lot of back and forth between engineers that discussed whether we should add the index.
Jeez. What was the idea behind not adding? Disk space I presume?
3 years ago
>There was a lot of back and forth between engineers that discussed whether we should add the index.
Jeez. What was the idea behind not adding? Disk space I presume?
There was a fear that having to create an index on a table that large would take a long time, and I think some of it was also ego "I intentionally didn't add it in, because so and so reason". This was why I dug in and did my thing, debunk all the fear / opinions / rationalization. Sometimes you just gotta be able to tell people they're wrong supported with empirical evidence. That's how the team will grow. There is just no need to dance around facts. I remember having to tell the team, "taking a long time to run an index is no reason to avoid creating the index".
>> I simply conducted a small experiment and PG analyze clearly showed a missing index in one of the key tables.
Based on this sentence, I interpreted that part as representing that the engineers did not believe the missing index was causing the problem (until the experiment was run).
Yes, one of the theory was that the index wasn't the problem because there was already a multi column index on that particular column. However the PG analyze tool showed some particular query didn't utilize the index, so there needed to be a separate index just for that particular column.
The number of reasons why an RDBMS - especially Postgres - can choose to not use an index is wide. Sometimes it’s your fault, sometimes it’s the table statistics fault.
Good on you for actually empirically determining reality.