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,
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,
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 the whole
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).
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
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
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 (though I'm
]
[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, remember
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
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 to
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,
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
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
to
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 kill the
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 filesystem
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.
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
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
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
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.
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 rewrite
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
22 matches
Mail list logo
