]
[mailto:[EMAIL PROTECTED] nombre de Joe
Enviado el: martes, 04 de octubre de 2005 18:11
Para: Jim C. Nasby
CC: Andreas Pflug; pgsql-performance@postgresql.org
Asunto: Re: [PERFORM] Comparative performance
Hi Jim,
Jim C. Nasby wrote:
> Also, just because no one else has mentioned it, remem
On Tue, Oct 04, 2005 at 05:11:19PM -0400, Joe wrote:
> Hi Jim,
>
> Jim C. Nasby wrote:
> >Also, just because no one else has mentioned it, remember that it's very
> >easy to get MySQL into a mode where you have no data integrity. If
> >that's the case it's going to be faster than PostgreSQL (thoug
Jim C. Nasby wrote:
Make sure these indexes exist if you'll be updating or inserting into
entry:
CREATE INDEX topic__subject_id ON topic(subject_id);
CREATE INDEX topic__actor_id ON topic(actor_id);
Actually, topic's primary key is topic_id.
Also, the fact that subject and actor both point t
PFC wrote:
- if you use a version before 8, type mismatch will prevent use of the
indexes.
I'm using 8.0.3, but the type mismatch between relationship.rel_type and
entry_type.type_id was unintended. The current databases use SMALLINT for both.
The PostgreSQL schema was derived from an expo
Hi Jim,
Jim C. Nasby wrote:
Also, just because no one else has mentioned it, remember that it's very
easy to get MySQL into a mode where you have no data integrity. If
that's the case it's going to be faster than PostgreSQL (though I'm not
sure how much that affects the performance of SELECTs).
On Thu, Sep 29, 2005 at 04:39:36PM -0400, Joe wrote:
> Andreas Pflug wrote:
> >Hm, if you only have 4 tables, why do you need 12 queries?
> >To reduce queries, join them in the query; no need to merge them
> >physically. If you have only two main tables, I'd bet you only need 1-2
> >queries for t
On Thu, Sep 29, 2005 at 08:44:16AM -0400, Joe wrote:
> CREATE TABLE entry (
> entry_id serial PRIMARY KEY,
> title VARCHAR(128) NOT NULL,
> subtitle VARCHAR(128),
> subject_type SMALLINT,
> subject_id INTEGER REFERENCES topic,
> actor_type SMALLINT,
> actor_id INTEGER REFERENCES topic
It's more understandable if the table names are in front of the column
names :
SELECT relationship.topic_id1, relationship.topic_id2, topic.topic_name,
topic.categ_id, topic.list_name, topic.title,
topic.url, topic.page_type, relationship.rel_type, entry_type.inverse_id,
relationship.desc
PFC wrote:
Even though this query isn't that optimized, it's still only 16
milliseconds.
Why does it take this long for PHP to get the results ?
Can you try pg_query'ing this exact same query, FROM PHP, and timing
it with getmicrotime() ?
That query took about 27 msec in actual
> > This smells like a TCP communication problem.
>
> I'm puzzled by that remark. How much does TCP get into the
> picture in a local Windows client/server environment?
Windows has no Unix Domain Sockets (no surprise there), so TCP
connections over the loopback interface are used to connect
PFC wrote:
Even though this query isn't that optimized, it's still only 16
milliseconds.
Why does it take this long for PHP to get the results ?
Can you try pg_query'ing this exact same query, FROM PHP, and timing
it with getmicrotime() ?
Thanks, that's what I was looking for.
Andreas Pflug wrote:
Hm, if you only have 4 tables, why do you need 12 queries?
To reduce queries, join them in the query; no need to merge them
physically. If you have only two main tables, I'd bet you only need 1-2
queries for the whole page.
There are more than four tables and the queries
Total runtime: 16.000 ms
Even though this query isn't that optimized, it's still only 16
milliseconds.
Why does it take this long for PHP to get the results ?
Can you try pg_query'ing this exact same query, FROM PHP, and timing it
with getmicrotime() ?
You can even do an E
I just tried using pg_pconnect() and I didn't notice any significant
improvement. What bothers me most is that with Postgres I tend to see
jerky behavior on almost every page: the upper 1/2 or 2/3 of the page
is displayed first and you can see a blank bottom (or you can see a
half-fill
Joe wrote:
The pages do use a number of queries to collect all the data for display
but nowhere near 50. I'd say it's probably less than a dozen.
The schema is fairly simple having two main tables: topic and entry
(sort of like account and transaction in an accounting scenario). There
Gavin Sherry wrote:
Please post the table definitions, queries and explain analyze results so
we can tell you why the performance is poor.
I did try to post that last night but apparently my reply didn't make it to the
list. Here it is again:
Matthew Nuzum wrote:
> This is the right list.
PFC wrote:
From my experience, the postgres libraries in PHP are a piece of
crap, and add a lot of overhead even from small queries.
For instance, a simple query like "SELECT * FROM table WHERE
primary_key_id=1234" can take the following time, on my laptop, with
data in the filesyste
On Thu, 29 Sep 2005, Joe wrote:
> Magnus Hagander wrote:
> > That actually depends a lot on *how* you use it. I've seen pg-on-windows
> > deployments that come within a few percent of the linux performance.
> > I've also seen those that are absolutely horrible compared.
> >
> > One sure way to kil
On Thu, Sep 29, 2005 at 08:16:11AM -0400, Joe wrote:
> I just tried using pg_pconnect() and I didn't notice any significant
> improvement.
PHP persistent connections are not really persistent -- or so I've been told.
Anyhow, what was discussed here was pg_query, not pg_connect. You really want
t
Magnus Hagander wrote:
That actually depends a lot on *how* you use it. I've seen pg-on-windows
deployments that come within a few percent of the linux performance.
I've also seen those that are absolutely horrible compared.
One sure way to kill the performance is to do a lot of small
connection
It appears that PostgreSQL is two to three times slower than MySQL. For
example, some pages that have some 30,000 characters (when saved as
HTML) take 1 to 1 1/2 seconds with MySQL but 3 to 4 seconds with
PostgreSQL. I had read that the former was generally faster than the
latter, parti
> > It appears that PostgreSQL is two to three times slower
> than MySQL.
> > For example, some pages that have some 30,000 characters
> (when saved
> > as HTML) take 1 to 1 1/2 seconds with MySQL but 3 to 4 seconds with
> > PostgreSQL. I had read that the former was generally
> faster than
On Wed, 28 Sep 2005, Joe wrote:
> Before I post the EXPLAIN and the table schema I'd appreciate
> confirmation that this list is the appropriate forum.
It is and and useful things to show are
* the slow query
* EXPLAIN ANALYZE of the query
* the output of \d for each table involved in the qu
On Wed, 28 Sep 2005, Joe wrote:
> I'm converting a relatively small database (2 MB) from MySQL to PostgreSQL.
> It
> is used to generate web pages using PHP. Although the actual website runs
> under
> Linux, the development is done under XP. I've completed most of the data
> conversion and re
I'm converting a relatively small database (2 MB) from MySQL to PostgreSQL. It
is used to generate web pages using PHP. Although the actual website runs under
Linux, the development is done under XP. I've completed most of the data
conversion and rewrite of the PHP scripts, so now I'm compari
25 matches
Mail list logo
