Thread: How to

Hello

I have a strange behaviour :
time psql -d db_cathline -c "SELECT * FROM v_stock_status;"

real 0m5.780s
user 0m0.090s
sys 0m0.020s

time psql -d db_cathline -c "SELECT * FROM v_stock_status WHERE remain_qty != 0;"

real 0m56.075s
user 0m0.070s
sys 0m0.030s

Why with the clause WHERE the time are 10x greater than ? How can I work around this ?

# select version();
                                                   version                                                  
-------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
(1 ligne)

Regards
Guy

Guy Deleeuw  wrote:

> Why with the clause WHERE the time are 10x greater than ? How can I
> work around this ?

Nobody can answer that without a bit more detail.  Could you show an
EXPLAIN ANALYZE of both queries and the definition of v_stock_status?
I that is a view, then we would need definitions of each table.  If
you provide that and the contents of postgresql.conf with all
comments stripped out, we can probably offer some suggestions.
Well, a description of your hardware wouldn't hurt, particularly in
terms of how much RAM you have and what your disk subsystem is like.

-Kevin

Guy Deleeuw  wrote:

My hardware : lapto hp envy I7 processor with a ssd disk

> Memory :
>                      total    used     free shared buffers cached
> Mem:               8129356 2866516  5262840      0  387172 948132
> -/+ buffers/cache:         1531212  6598144
> Swap:             15625208       0 15625208

It looks like everything you're running, including caching the entire
database, or at least the active part of it, fits in less than 3 GB
on your 8 GB machine.

> shared_buffers = 24MB

Given your hardware, I would bump that to 2GB or so.

I would also set effective_cache_size to 7GB.  I would set work_mem
to at *least* 50MB; if you're only using a few connections, you could
easily bump that to 100MB to 200MB.  Fully cached, especially if it's
all on PostgreSQL shared memory, seq_page_cost and random_page_cost
both to 0.05.  Between the SSD and having all data in shared memory,
you might want to go even lower.  There is some other general tuning
to be done, but the above will make the most difference for read-only
queries, which is what you're asking about.

[nested views, using subqueries]

You should probably look at reqorking the v_stock view to use joins
instead of subqueries -- they often optimize much better.

I didn't spend a lot of time with the EXPLAIN ANALYZE output, because
if you configure PostgreSQL to get costing factors more in line with
actual costs for that hardware, you're likely to see totally
different plans.  And I can pretty much guarantee that if you tune
the costs *and* rework that view to use JOINs, you'll get completely
different plans which will perform *much* better.

I hope this helps.

-Kevin

Hello Kevin,
>
> You should probably look at reqorking the v_stock view to use joins
> instead of subqueries -- they often optimize much better.
This is the problem effectively.
For creating this view I use a query tools that generate the code, not a
good idea.
I update and work now :-)
Best regards
Guy

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1



On 06/02/2011 17:30, Kevin Grittner wrote:
> Guy Deleeuw  wrote:
>
> My hardware : lapto hp envy I7 processor with a ssd disk
>
>> Memory :
>>                      total    used     free shared buffers cached
>> Mem:               8129356 2866516  5262840      0  387172 948132
>> -/+ buffers/cache:         1531212  6598144
>> Swap:             15625208       0 15625208
>
> It looks like everything you're running, including caching the entire
> database, or at least the active part of it, fits in less than 3 GB
> on your 8 GB machine.
>
>> shared_buffers = 24MB
>
> Given your hardware, I would bump that to 2GB or so.
>
> I would also set effective_cache_size to 7GB.

Here I think it is too much because when the 2 GB of shared memory will
be used, only 6 GB of RAM will be left and maybe not entirely used for
file system cache. My way to find effective_cache_size value is to put
it to 50% of the amount of ram, drop the cache after tuning, leaving the
server live for a while and have a look at the output of free to find
the final value.

> I would set work_mem
> to at *least* 50MB; if you're only using a few connections, you could
> easily bump that to 100MB to 200MB.  Fully cached, especially if it's
> all on PostgreSQL shared memory, seq_page_cost and random_page_cost
> both to 0.05.  Between the SSD and having all data in shared memory,
> you might want to go even lower.  There is some other general tuning
> to be done, but the above will make the most difference for read-only
> queries, which is what you're asking about.
>
> [nested views, using subqueries]
>
> You should probably look at reqorking the v_stock view to use joins
> instead of subqueries -- they often optimize much better.
>
> I didn't spend a lot of time with the EXPLAIN ANALYZE output, because
> if you configure PostgreSQL to get costing factors more in line with
> actual costs for that hardware, you're likely to see totally
> different plans.  And I can pretty much guarantee that if you tune
> the costs *and* rework that view to use JOINs, you'll get completely
> different plans which will perform *much* better.
>
> I hope this helps.
>
> -Kevin
>

- --
Nicolas Thauvin
DBA
http://www.dalibo.com
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.10 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQIcBAEBAgAGBQJNUDJZAAoJEDa2VZzWZKpN/L0P/i1/C7VRz1+vo1itkO3/VD9+
AMvErAsEh5TDfDM8/a4pZ6f6MVZzDxBf5+MB1kOTInUzhj48mS5+XHYJbdmzeRHy
tNCCHhGGQ7M6l7W3cOCKh1tytumPuGzCw4rzFLi0/3IwBwi52AfnW+ibRSjXxGxU
2heAOwDc6amxTaocthuYna4lN+tgp23R3fENl6p+/2cGgN2WwXH1UytAUyH2OTFQ
fxUZmyn7VUDnWb6u/vLsjVtAPz5Zq7Syc+VA0F886RFVguqau0tpHVI35Cqfu6rj
Rd9LTC5+FkD1HiXGBWimEzJX6RfPaQ/Yq5TzVWqIZcUm7DIREEwZhfp+fiUeZzV+
XRK1aaTuREGNXaRp/ISsVrJI2Hp9lpX+qLrggWRNZpSJaSa03foS5kYBxA8Nnmbh
M6qZIKTOC0gt0FiNdhONe8+kblEsLUMlEWS3RO6FXyHqCUsBi7kQOc0/07GLY52K
m0lbWJ2QUEGc2uto1z1bReF+DK2+pxHVIZ0RF4yv+0OcM/ERVi/kn6pTHslhMOwP
YFpAGaQ+3AflIvwq0WulqbnStuoMpDlCypic1s6sDshkkFMnQczDGhj0cI6fl+tP
yPvuM14tpZV148LwZ1OGR7DoCi4cHbWuXcOuJ0R4hWDHUXtcmtsSetn4MBC8VS2O
Ln484THmHEmLAmmu/rt6
=Rdav
-----END PGP SIGNATURE-----

Nicolas Thauvin <nicolas.thauvin@dalibo.com> wrote:
> On 06/02/2011 17:30, Kevin Grittner wrote:
>> Guy Deleeuw  wrote:
>>
>> My hardware : lapto hp envy I7 processor with a ssd disk
>>
>>> Memory :
>>>                  total    used     free shared buffers cached
>>>                8129356 2866516  5262840      0  387172 948132
>>> -/+ buffers/cache:     1531212  6598144
>>> Swap:         15625208       0 15625208
>>
>> It looks like everything you're running, including caching the
>> entire database, or at least the active part of it, fits in less
>> than 3 GB on your 8 GB machine.
>>
>>> shared_buffers = 24MB
>>
>> Given your hardware, I would bump that to 2GB or so.
>>
>> I would also set effective_cache_size to 7GB.
>
> Here I think it is too much because when the 2 GB of shared memory
> will be used, only 6 GB of RAM will be left and maybe not entirely
> used for file system cache. My way to find effective_cache_size
> value is to put it to 50% of the amount of ram, drop the cache
> after tuning, leaving the server live for a while and have a look
> at the output of free to find the final value.

Given that the usage of effective_cache_size seems to be an estimate
of how much will be effectively cached if a single query pounds away
at an index with random reads, adding shared buffers to the space
available for OS caching seems reasonable to me, and doing so
doesn't ever seem to have caused me problems.  On the other hand,
any size larger than your total database will be as effective as any
other size larger than your database.  ;-)

For me, the database is usually bigger than RAM, so I just add
shared_buffers to whatever "free" shows as cached.

-Kevin