Search Postgresql Archives

Re: Index size

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




On Mon, Dec 5, 2016 at 8:06 PM, Samuel Williams <space.ship.traveller@xxxxxxxxx> 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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux