Re: PHP Postgres query slower then PgAdmin

Hello,

I'm using php5.4.12 with extension=php_pgsql.dll enabled but the query that
i'm trying to execute is faster on PgAdmin app.

SELECT title, ts_rank_cd(vector, query) AS rank FROM links,
to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY rank
DESC;

I'm not sure, what can i do to increase the speed of execution from php:

$start_time = microtime(true);
$query = "SELECT title, url, ts_rank_cd(vector, query) AS rank FROM links,
to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY rank
DESC LIMIT 10;";
$result = pg_query($connection, $query);
$end_time = microtime(true);

persistant connections are enabled in php.ini but i calculate only
execution time from start to end.

Thanks, Emrah.

--
Best regards, Emrah Mehmedov
Software Developer @ X3M Labs
http://www.extreme-labs.com

Is php connecting through tcp whilst pgadmin is using unix domain socket?
Probably the query time is the same, but returning the result over tcp
will be slower.

On 5 June 2013 11:18, Emrah Mehmedov <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:

> Hello,
>
> I'm using php5.4.12 with extension=php_pgsql.dll enabled but the query
> that i'm trying to execute is faster on PgAdmin app.
>
> SELECT title, ts_rank_cd(vector, query) AS rank FROM links,
> to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY rank
> DESC;
>
> I'm not sure, what can i do to increase the speed of execution from php:
>
> $start_time = microtime(true);
> $query = "SELECT title, url, ts_rank_cd(vector, query) AS rank FROM links,
> to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY rank
> DESC LIMIT 10;";
> $result = pg_query($connection, $query);
> $end_time = microtime(true);
>
> persistant connections are enabled in php.ini but i calculate only
> execution time from start to end.
>
> Thanks, Emrah.
>
> --
> Best regards, Emrah Mehmedov
> Software Developer @ X3M Labs
> http://www.extreme-labs.com
>

Can we modify php connection?

On Wed, Jun 5, 2013 at 1:01 PM, Bob Jolliffe <bobjolliffe(at)gmail(dot)com> wrote:

> Is php connecting through tcp whilst pgadmin is using unix domain socket?
> Probably the query time is the same, but returning the result over tcp
> will be slower.
>
>
> On 5 June 2013 11:18, Emrah Mehmedov <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
>
>> Hello,
>>
>> I'm using php5.4.12 with extension=php_pgsql.dll enabled but the query
>> that i'm trying to execute is faster on PgAdmin app.
>>
>> SELECT title, ts_rank_cd(vector, query) AS rank FROM links,
>> to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY rank
>> DESC;
>>
>> I'm not sure, what can i do to increase the speed of execution from php:
>>
>> $start_time = microtime(true);
>> $query = "SELECT title, url, ts_rank_cd(vector, query) AS rank FROM
>> links, to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY
>> rank DESC LIMIT 10;";
>> $result = pg_query($connection, $query);
>> $end_time = microtime(true);
>>
>> persistant connections are enabled in php.ini but i calculate only
>> execution time from start to end.
>>
>> Thanks, Emrah.
>>
>> --
>> Best regards, Emrah Mehmedov
>> Software Developer @ X3M Labs
>> http://www.extreme-labs.com
>>
>
>

--
Best regards, Emrah Mehmedov
Software Developer @ X3M Labs
http://www.extreme-labs.com

Most probably. If your existing connection string specifies something like
"host=localhost port=5432 ..." just remove the host and port parameters and
php will by default try to connect with unix domain socket.

On 5 June 2013 12:02, Emrah Mehmedov <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:

> Can we modify php connection?
>
>
> On Wed, Jun 5, 2013 at 1:01 PM, Bob Jolliffe <bobjolliffe(at)gmail(dot)com>wrote:
>
>> Is php connecting through tcp whilst pgadmin is using unix domain socket?
>> Probably the query time is the same, but returning the result over tcp
>> will be slower.
>>
>>
>> On 5 June 2013 11:18, Emrah Mehmedov <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
>>
>>> Hello,
>>>
>>> I'm using php5.4.12 with extension=php_pgsql.dll enabled but the query
>>> that i'm trying to execute is faster on PgAdmin app.
>>>
>>> SELECT title, ts_rank_cd(vector, query) AS rank FROM links,
>>> to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY rank
>>> DESC;
>>>
>>> I'm not sure, what can i do to increase the speed of execution from php:
>>>
>>> $start_time = microtime(true);
>>> $query = "SELECT title, url, ts_rank_cd(vector, query) AS rank FROM
>>> links, to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY
>>> rank DESC LIMIT 10;";
>>> $result = pg_query($connection, $query);
>>> $end_time = microtime(true);
>>>
>>> persistant connections are enabled in php.ini but i calculate only
>>> execution time from start to end.
>>>
>>> Thanks, Emrah.
>>>
>>> --
>>> Best regards, Emrah Mehmedov
>>> Software Developer @ X3M Labs
>>> http://www.extreme-labs.com
>>>
>>
>>
>
>
> --
> Best regards, Emrah Mehmedov
> Software Developer @ X3M Labs
> http://www.extreme-labs.com
>

I notice something in CMD, first time query is executing same time like
from php all the time, but on the rest of the time that i will execute the
query is faster from cmd, php is keeping the same execution time.
i also change the connection string (i remove host and port) and nothing
changed.

On Wed, Jun 5, 2013 at 1:11 PM, Bob Jolliffe <bobjolliffe(at)gmail(dot)com> wrote:

> Most probably. If your existing connection string specifies something
> like "host=localhost port=5432 ..." just remove the host and port
> parameters and php will by default try to connect with unix domain socket.
>
>
> On 5 June 2013 12:02, Emrah Mehmedov <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
>
>> Can we modify php connection?
>>
>>
>> On Wed, Jun 5, 2013 at 1:01 PM, Bob Jolliffe <bobjolliffe(at)gmail(dot)com>wrote:
>>
>>> Is php connecting through tcp whilst pgadmin is using unix domain
>>> socket? Probably the query time is the same, but returning the result over
>>> tcp will be slower.
>>>
>>>
>>> On 5 June 2013 11:18, Emrah Mehmedov <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
>>>
>>>> Hello,
>>>>
>>>> I'm using php5.4.12 with extension=php_pgsql.dll enabled but the query
>>>> that i'm trying to execute is faster on PgAdmin app.
>>>>
>>>> SELECT title, ts_rank_cd(vector, query) AS rank FROM links,
>>>> to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY rank
>>>> DESC;
>>>>
>>>> I'm not sure, what can i do to increase the speed of execution from php:
>>>>
>>>> $start_time = microtime(true);
>>>> $query = "SELECT title, url, ts_rank_cd(vector, query) AS rank FROM
>>>> links, to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY
>>>> rank DESC LIMIT 10;";
>>>> $result = pg_query($connection, $query);
>>>> $end_time = microtime(true);
>>>>
>>>> persistant connections are enabled in php.ini but i calculate only
>>>> execution time from start to end.
>>>>
>>>> Thanks, Emrah.
>>>>
>>>> --
>>>> Best regards, Emrah Mehmedov
>>>> Software Developer @ X3M Labs
>>>> http://www.extreme-labs.com
>>>>
>>>
>>>
>>
>>
>> --
>> Best regards, Emrah Mehmedov
>> Software Developer @ X3M Labs
>> http://www.extreme-labs.com
>>
>
>

--
Best regards, Emrah Mehmedov
Software Developer @ X3M Labs
http://www.extreme-labs.com

Just rule out something weird;

Can you determine if you are getting the same query plan in both cases? Use
"explain analyze" from the command line and turn on auto.explain via
postgresql.conf to log what the plan is for the php case.

Tom Kincaid
EnterpriseDB
www.enterprisedb.com

On Wed, Jun 5, 2013 at 7:15 AM, Emrah Mehmedov
<emrah(dot)mehmedov(at)x3mlabs(dot)com>wrote:

> I notice something in CMD, first time query is executing same time like
> from php all the time, but on the rest of the time that i will execute the
> query is faster from cmd, php is keeping the same execution time.
> i also change the connection string (i remove host and port) and nothing
> changed.
>
>
> On Wed, Jun 5, 2013 at 1:11 PM, Bob Jolliffe <bobjolliffe(at)gmail(dot)com>wrote:
>
>> Most probably. If your existing connection string specifies something
>> like "host=localhost port=5432 ..." just remove the host and port
>> parameters and php will by default try to connect with unix domain socket.
>>
>>
>> On 5 June 2013 12:02, Emrah Mehmedov <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
>>
>>> Can we modify php connection?
>>>
>>>
>>> On Wed, Jun 5, 2013 at 1:01 PM, Bob Jolliffe <bobjolliffe(at)gmail(dot)com>wrote:
>>>
>>>> Is php connecting through tcp whilst pgadmin is using unix domain
>>>> socket? Probably the query time is the same, but returning the result over
>>>> tcp will be slower.
>>>>
>>>>
>>>> On 5 June 2013 11:18, Emrah Mehmedov <emrah(dot)mehmedov(at)x3mlabs(dot)com>wrote:
>>>>
>>>>> Hello,
>>>>>
>>>>> I'm using php5.4.12 with extension=php_pgsql.dll enabled but the query
>>>>> that i'm trying to execute is faster on PgAdmin app.
>>>>>
>>>>> SELECT title, ts_rank_cd(vector, query) AS rank FROM links,
>>>>> to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY rank
>>>>> DESC;
>>>>>
>>>>> I'm not sure, what can i do to increase the speed of execution from
>>>>> php:
>>>>>
>>>>> $start_time = microtime(true);
>>>>> $query = "SELECT title, url, ts_rank_cd(vector, query) AS rank FROM
>>>>> links, to_tsquery('english', 'risk') query WHERE query @@ vector ORDER BY
>>>>> rank DESC LIMIT 10;";
>>>>> $result = pg_query($connection, $query);
>>>>> $end_time = microtime(true);
>>>>>
>>>>> persistant connections are enabled in php.ini but i calculate only
>>>>> execution time from start to end.
>>>>>
>>>>> Thanks, Emrah.
>>>>>
>>>>> --
>>>>> Best regards, Emrah Mehmedov
>>>>> Software Developer @ X3M Labs
>>>>> http://www.extreme-labs.com
>>>>>
>>>>
>>>>
>>>
>>>
>>> --
>>> Best regards, Emrah Mehmedov
>>> Software Developer @ X3M Labs
>>> http://www.extreme-labs.com
>>>
>>
>>
>
>
> --
> Best regards, Emrah Mehmedov
> Software Developer @ X3M Labs
> http://www.extreme-labs.com
>

--
Thomas John

On Wed, Jun 5, 2013 at 1:15 PM, Emrah Mehmedov
<emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
> [ull text search]
> I notice something in CMD, first time query is executing same time like from
> php all the time, but on the rest of the time that i will execute the query
> is faster from cmd, php is keeping the same execution time.
> i also change the connection string (i remove host and port) and nothing
> changed.

The first query using a text search config loads the dictionaries, so
it is slower, and that's why the following queries are faster. I think
your PHP persistent connections don't work too well.

Regards
Marcin Mańk

Marcin: This heppens everytime when i try to query different keyword in FTS
for example:

firsttime: query: "Hello & World" 15sec~
secondtime: query: "Hello & World" 2-3sec

then new query

firsttime: query: "We & are & good" 10sec~
secondtime: query: "We & are & good" 2-3sec

even if i'm going from CMD queries are faster but on first time always it's
slower then rest of the times.

On Sun, Jun 23, 2013 at 9:57 PM, Marcin Mańk <marcin(dot)mank(at)gmail(dot)com> wrote:

> On Wed, Jun 5, 2013 at 1:15 PM, Emrah Mehmedov
> <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
> > [ull text search]
> > I notice something in CMD, first time query is executing same time like
> from
> > php all the time, but on the rest of the time that i will execute the
> query
> > is faster from cmd, php is keeping the same execution time.
> > i also change the connection string (i remove host and port) and nothing
> > changed.
>
> The first query using a text search config loads the dictionaries, so
> it is slower, and that's why the following queries are faster. I think
> your PHP persistent connections don't work too well.
>
> Regards
> Marcin Mańk
>

--
Best regards, Emrah Mehmedov
Software Developer @ X3M Labs
http://www.extreme-labs.com

On Mon, Jun 24, 2013 at 11:55 AM, Emrah Mehmedov
<emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
> Marcin: This heppens everytime when i try to query different keyword in FTS
> for example:
>
> firsttime: query: "Hello & World" 15sec~
> secondtime: query: "Hello & World" 2-3sec
>
> then new query
>
> firsttime: query: "We & are & good" 10sec~
> secondtime: query: "We & are & good" 2-3sec
>
Now it looks like Postgres is fetching data from disk on first query
run, the second time it is from cache, so faster. Try:

EXPLAIN(ANALYZE, BUFFERS) SELECT title, url, ts_rank_cd(vector, query)
AS rank FROM links, to_tsquery('english', 'risk') query WHERE query @@
vector ORDER BY rank DESC LIMIT 10;

with varying queries, and post the results. This will show how many
blocks are are read from shared buffers, and how many are read from
the OS(either from OS disk cache, or the actual disk).

Regards
Marcin Mańk

On Mon, Jun 24, 2013 at 12:04 PM, Marcin Mańk <marcin(dot)mank(at)gmail(dot)com> wrote:

> On Mon, Jun 24, 2013 at 11:55 AM, Emrah Mehmedov
> <emrah(dot)mehmedov(at)x3mlabs(dot)com> wrote:
> > Marcin: This heppens everytime when i try to query different keyword in
> FTS
> > for example:
> >
> > firsttime: query: "Hello & World" 15sec~
> > secondtime: query: "Hello & World" 2-3sec
> >
> > then new query
> >
> > firsttime: query: "We & are & good" 10sec~
> > secondtime: query: "We & are & good" 2-3sec
> >
> Now it looks like Postgres is fetching data from disk on first query
> run, the second time it is from cache, so faster. Try:
>
> EXPLAIN(ANALYZE, BUFFERS) SELECT title, url, ts_rank_cd(vector, query)
> AS rank FROM links, to_tsquery('english', 'risk') query WHERE query @@
> vector ORDER BY rank DESC LIMIT 10;
>
> with varying queries, and post the results. This will show how many
> blocks are are read from shared buffers, and how many are read from
> the OS(either from OS disk cache, or the actual disk).
>
> Regards
> Marcin Mańk
>

Hi Marcin Mańk,

i run the query with analyze and explain and the time is pretty same as i
calculate in php code, solution is to improve query or FTS dictionaries.

Thank you.
--
Best regards, Emrah Mehmedov
Software Developer @ X3M Labs
http://www.extreme-labs.com