On Mon, Dec 5, 2016 at 8:06 PM, Samuel Williams < space.ship.travel...@gmail.com> wrote:
> Melvin, uh... I'm a software engineer... since when was it a problem to > want to know how things work and why they are different? If you have > nothing to contribute of a relevant technical nature, please don't reply, > I'm really not interested..... > Samuel, >since when was it a problem to want to know how things work and why they are different? I am a retired DBA/Software Engineer/Customer Engineer. I have worked all aspects of IT. Hardware, Software, Database, etc. I never said there was a problem wanting to know the difference between things. What I did say was that there IS a difference. You are concentrating your inquiry in the wrong area. If you want to know something specific about a PostgreSQL index, then ask that, but don't expect anyone to explain why it's different from a MySQL index, because there is no point in it. > Could it be I should add WHERE the fields are not null? That depends on the queries they will be used with. If you have a WHERE clause in your query that requires a not null field, then the index will be useful, providing the table is large enough to make the index scan faster than a sequential scan. So if you have a query like: SELECT id, user_id, poi_id FROM user_event WHERE deal_id IS NULL; Then, yes, adding a WHERE clause to index_user_event_on_deal_id for NULL deal_id's is fine. But if you want SELECT id, user_id, poi_id FROM user_event WHERE deal_id = <some_value> ; Then you don't need the WHERE deal_id IS NULL in your index. BTW, most dba's prefer simpler, shorter, index names. EG: idx_user_event_deal_id -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
