I moved to PostgreSQL from using MySQL, and [...] I just thought it was a bigger, more complicated database that did 'the same stuff' as MySQL. It took me a while to really realize how great the 'other' features are.
Good luck!

PostgreSQL is pronounced Post -Gres -Q -L.

If you find 'PostgreSQL' hard to pronounce, call it 'Postgres' instead.
Source: wiki.postgresql.org

Is PostgreSQL better than MySQL

Yesn't

In common

• Window functions, Common Table Expressions, Recursive Queries, Aggregates for strings, Tuple comparison, Multi-row INSERTs, Enums, Session local temporary table, Row level triggers

MariaDB

• Temporal queries Temporal queries allow querying the database (or a single table) to return the data as it was in the past
• Computed columns Define a column in a table that is always calculated based on other columns
• Add table column at specific position

PostgreSQL

• Filtered aggregates
  Only include rows in an aggregate based on a condition: avg(salary) filter (where dept_id = 1)
• Partial index
  Define an index on a subset of a table
• Index on expression
  Create an index based on an expression/function
• User defined datatypes
  Crate UDTs using SQL and use those UDTs as a column's data type

PostgreSQL

• Arrays, IP address, BOOLEAN (not Tinyint :p ), Range types
• Materialized views
• LATERAL JOIN
  Get Multiple Columns and Ouside Reference from a Subquery
• Key/Value storage

Perfomance Comparison

It depends

Comparison Functions and Operators

• <, >, <=, >=, <> or !=
• a BETWEEN (symmetric) x AND y
• expression IS NULL
• boolean_expression IS NOT FALSE

between symmetric

> select * from generate_series(1,10) as numbers(a)
where numbers.a between 5 and 3;
a
---

> select * from generate_series(1,10) as numbers(a)
where numbers.a between symmetric 5 and 3;
a
---
3
4
5

Date/Time Functions and Operators

• age(timestamp, timestamp)
• OVERLAPS

age and overlaps

                    
select age(timestamp '2001-04-10', timestamp '1957-06-13');
result: 43 years 9 mons 27 days

SELECT (DATE '2001-02-16', DATE '2001-12-21') OVERLAPS
(DATE '2001-10-30', DATE '2002-10-30');
Result: true
                    
                

Array Functions and Operators

• =, <>, @>, ||
• array_length(anyarray, int)
• array_append(anyarray, anyelement)
• array_remove(anyarray, anyelement)

                    
ARRAY[1.1,2.1,3.1]::int[] = ARRAY[1,2,3] => true
ARRAY[1,4,3] @> ARRAY[3,1] => true
ARRAY[1,2,3] || ARRAY[4,5,6] => {1,2,3,4,5,6}
array_length(array[1,2,3], 1) => 3
array_remove(ARRAY[1,2,3,2], 2) => {1,3}
                    
                

Aggregate Functions

• array_agg()
• avg()
• count()
• every()
• min()
• max()
• sum()

                    
SELECT min(a), max(a), sum(a),
    every(a<11), every(a=3),
    array_agg(a), avg(a), count(a)
FROM generate_series(1,10) as a;
---
| min | max | sum | every | every |
|  1  | 10  | 55  | true  | false |

| array_agg              | avg | count |
| {1,2,3,4,5,6,7,8,9,10} | 5.5 | 10    |
                    
                

Subquery Expressions

• EXISTS
• IN
• NOT IN
• ANY || SOME
• ALL

                    
SELECT array_agg(a)
FROM generate_series(1,4) as a
where a not in(select generate_series(3,10))
{1,2}

SELECT array_agg(a)
FROM generate_series(1,4) as a
where a > any(select generate_series(1,2))
{2,3,4}
                    
                

• B-tree
• Hash
• GiST / SP-GiST
• GIN
• BRIN

B-tree

• <, <=, =, >=, >
• BETWEEN
• IN
• IS NULL

Hash

=

GiST

for rows that overlap values

• Geometry types
• Geographical data
• Text when dealing with full-text search

GIN

for columns with multiple values

• Arrays
• Range Types
• JSONB

BRIN

for large narturally sortable data

• Times
• Dates
• Zip codes

Analyse

• What -> Profiling
• Where -> Debug
• Why -> Explain

PHP Profiling

• xDebug
• enable profiling in oneOfYourPhp.ini
• open result in data vizualizer like KCachegrind
• find parts that are a) taking much time b) are called very often

PHP Debugging

• xDebug
• enable debugging in youShouldAlreadyKnow.ini
• stop at interresting parts
• extract queries from runtime

PostgreSQL Explain

EXPLAIN SELECT * FROM tenk1;

                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..458.00 rows=10000 width=244)

PostgreSQL Explain Analyze

EXPLAIN ANALYZE SELECT * FROM tenk1 WHERE ten < 7;

                           QUERY PLAN
------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..483.00 rows=7000 width=244)
            (actual time=0.016..5.107 rows=7000 loops=1)
   Filter: (ten < 7)
   Rows Removed by Filter: 3000
 Planning time: 0.083 ms
 Execution time: 5.905 ms

Ways to Improve Queries

• Eliminate Sequencial Scans on "larger" tables
• Avoid LIKE
• Avoid function calls in WHERE clause
• Avoid large IN() statements
• Convert subqueries to JOIN statements
• Use EXISTS when checking for existence of rows