Like anything else, it appears PostgreSQL has a lot more effective/complex indexing for tables. Possibly someone might help me to understand the default method to index posts.
Automatically I am talking about integer/boolean posts that are used 90% of times to filter table results.
In MySQL I'd simply create a catalog on the column with similar title as that column. I am unsure what kind was adopted (btree?) or exactly what the implications of naming the index exactly the same factor because the column were - however it labored.
Now moving to PostgreSQL I am wondering if there's any difficulty naming the index with similar title (or any reason much less). Also, I am wondering which kind of index ought to be employed for int/bool values.
Typically the default index type (btree) is going to be fine.
The default index title (if you do not specify one out of the CREATE INDEX statement) is dependant on the table and column title, typically something similar to table_column_idx. IIRC index names should be unique inside a schema, if you title your indexes with similar title because the posts you might encounter trouble when the same column title can be used in several table within the schema.
If you wish to index booleans make use of a conditional index. Most boolean values are generally evenly distruted, by which situation a catalog on just boole gains you next to nothing, since you will be reading through half one of the ways or another. But, in situations where you've got a boole where 99.999% from the rows is one value and also the other .001% would be the other value, creating a catalog on Exactly that .001% is sensible:
create index mostly_true on tablename (somefieldIwantwhenboolfieldisfalse) where boolfield is fake
Observe that this works best for multicolumn indexes.
create index mostly_true on tablename (col1,col2) where boolfield is fake