Обсуждение: select performance.
Hi All,
My application does select with were condition using IN function on only one table and I see slow response from front end. Please clarify me, do we have options like memory tables or is it possible to pin entire table in memory or do we have any option to improve only select statement.
-Dhandapani
Hi Dhandapani, first of all, I do not think is a good idea to measure the response time from the frontend. Run the query on the database and time it. Also an EXPLAIN ANALYZE of the query will give you hints on what is going on. Please post that too. Additionally, you could tell us a bit more about your tables structure, the data in it, the machine it runs on, your OS, your Postgres version and how Postgres is configured. This said, if your data resides in memory, data retrieval will indeed be faster, but is not a 5 minutes job, using a silver bullet. It requires analysis. Let us know more, and we can probably tell you more. regards, fabio pardi On 08/10/2018 10:12 AM, Dhandapani Shanmugam wrote: > Hi All, > > My application does select with were condition using IN function on only > one table and I see slow response from front end. Please clarify me, do > we have options like memory tables or is it possible to pin entire table > in memory or do we have any option to improve only select statement. > > -Dhandapani
Thanks for your response Fabio.
>>
Since the select queries has $ parameters, I could not able to do the EXPLAIN ANALYZE on that for example the select query is like below.
SELECT ... in ($3, $4, $5, $6)
>> PostgreSQL version 10, runs in CENTOS 7.2 with 64 GB of RAM. The table has only "character varying" and "numeric" as data type
-Dhandapani.
On Fri, Aug 10, 2018 at 2:05 PM, Fabio Pardi <f.pardi@portavita.eu> wrote:
Hi Dhandapani,
first of all, I do not think is a good idea to measure the response time
from the frontend. Run the query on the database and time it.
Also an EXPLAIN ANALYZE of the query will give you hints on what is
going on. Please post that too.
Additionally, you could tell us a bit more about your tables structure,
the data in it, the machine it runs on, your OS, your Postgres version
and how Postgres is configured.
This said, if your data resides in memory, data retrieval will indeed be
faster, but is not a 5 minutes job, using a silver bullet. It requires
analysis.
Let us know more, and we can probably tell you more.
regards,
fabio pardi
On 08/10/2018 10:12 AM, Dhandapani Shanmugam wrote:
> Hi All,
>
> My application does select with were condition using IN function on only
> one table and I see slow response from front end. Please clarify me, do
> we have options like memory tables or is it possible to pin entire table
> in memory or do we have any option to improve only select statement.
>
> -Dhandapani
You could look into the application and pick up the passed parameters. Another way to find out the parameters is to set 'log_min_duration_statement' to 0 it will log the slow query, the duration and the parameters too. When you find it out, please post also the server configuration (the settings that are not default). regards, fabio pardi On 08/10/2018 10:48 AM, Dhandapani Shanmugam wrote: > > Thanks for your response Fabio. > >>> > Since the select queries has $ parameters, I could not able to do the > EXPLAIN ANALYZE on that for example the select query is like below. > > SELECT ... in ($3, $4, $5, $6) > >>> PostgreSQL version 10, runs in CENTOS 7.2 with 64 GB of RAM. The > table has only "character varying" and "numeric" as data type > > > -Dhandapani. > > On Fri, Aug 10, 2018 at 2:05 PM, Fabio Pardi <f.pardi@portavita.eu > <mailto:f.pardi@portavita.eu>> wrote: > > Hi Dhandapani, > > first of all, I do not think is a good idea to measure the response time > from the frontend. Run the query on the database and time it. > > Also an EXPLAIN ANALYZE of the query will give you hints on what is > going on. Please post that too. > > Additionally, you could tell us a bit more about your tables structure, > the data in it, the machine it runs on, your OS, your Postgres version > and how Postgres is configured. > > This said, if your data resides in memory, data retrieval will indeed be > faster, but is not a 5 minutes job, using a silver bullet. It requires > analysis. > > Let us know more, and we can probably tell you more. > > regards, > > fabio pardi > > > > On 08/10/2018 10:12 AM, Dhandapani Shanmugam wrote: > > Hi All, > > > > My application does select with were condition using IN function > on only > > one table and I see slow response from front end. Please clarify > me, do > > we have options like memory tables or is it possible to pin entire > table > > in memory or do we have any option to improve only select statement. > > > > -Dhandapani > >