On Mon, Feb 16, 2015 at 8:27 AM, Valentina Tedone <valetedo88@gmail.com> wrote:
> Hi!
>
> I am new to Postgresql.
> I want to tune my Postgresql config, I used pgtune
> (http://pgtune.leopard.in.ua/) to set my parameters, is it the best way to
> set them?
> I want to improve the database performance (time) choosing the best
> configuration for my db.
> The parameters of my system are: Ubuntu LTS 12.04 and 3635 MB of RAM.
The absolute best thing to do if you're new to performance tuning
postgres and dbs in general is to get Greg Smith's Performance
PostgreSQL book.
> And then : why does "ORDER BY" increase the query execution time ?
Because ordering random things takes time, and that time goes up
non-linearly as the list gets bigger.
>
> I have 3 dbs, the first one is 108 MB, the second one is 1200 MB and the
> last one is 11 GB, why in the first two dbs to solve a query does it use the
> bitmap heap scan, instead in the last one it uses the seq scan?
Because the query planner is "smart". I.e. it looks at the size of the
data, the indexes it has, etc and decides on the fly which plan is
best.
You can tune the query planner by changing certain parameters, like
random_page_cost, seq_page_cost, effective_cache_size, cpu_tuple*
costs and so on.
But seriously, go buy Greg Smith's book first.
https://www.packtpub.com/big-data-and-business-intelligence/postgresql-90-high-performance
It was written for 9.0 and is now a bit behind what with 9.4 out but
the majority of it still applies and you can learn a LOT of what it
takes to make a db fast.