pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSpeed or configuration
9+ messages / 4 participants
[nested] [flat]
* Speed or configuration
@ 2000-08-20 16:05 Franz J Fortuny <ffortuny@ivsol.com>
0 siblings, 3 replies; 9+ messages in thread
From: Franz J Fortuny @ 2000-08-20 16:05 UTC (permalink / raw)
To: pgsql-sql
At our company we are presently using a commercial
database that generates results from this query:
select xx1,xx2,xx3 from tableX
where field1 in
(select field1 from tableY where
field2=NNN and field3=NNN2 and field4=NNN4)
tableX has 790,000 rows, and an index on field1
tableY has abou 175,000 rows and an index that includes
field2,field3,field4 (and 2 other fields not being used
here)
Of course, the order in the indexes is the logical one.
I have made copies of the tables from the commercial SQL
server to PostgreSQL and PostgreSQL is consistently
faster in things like count(*) and certain other
queries.
But when it comes to the above mentioned query,
PostgreSQL simply stays there, forever. The postgres
backend must be killed in order to free the client
program.
Any suggestions?
Franz J Fortuny
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Speed or configuration
@ 2000-08-20 17:59 The Hermit Hacker <scrappy@hub.org>
parent: Franz J Fortuny <ffortuny@ivsol.com>
2 siblings, 1 reply; 9+ messages in thread
From: The Hermit Hacker @ 2000-08-20 17:59 UTC (permalink / raw)
To: Franz J Fortuny <ffortuny@ivsol.com>; +Cc: pgsql-sql
On Sun, 20 Aug 2000, Franz J Fortuny wrote:
> At our company we are presently using a commercial
> database that generates results from this query:
>
> select xx1,xx2,xx3 from tableX
> where field1 in
> (select field1 from tableY where
> field2=NNN and field3=NNN2 and field4=NNN4)
>
> tableX has 790,000 rows, and an index on field1
> tableY has abou 175,000 rows and an index that includes
> field2,field3,field4 (and 2 other fields not being used
> here)
>
> Of course, the order in the indexes is the logical one.
>
> I have made copies of the tables from the commercial SQL
> server to PostgreSQL and PostgreSQL is consistently
> faster in things like count(*) and certain other
> queries.
>
> But when it comes to the above mentioned query,
> PostgreSQL simply stays there, forever. The postgres
> backend must be killed in order to free the client
> program.
>
> Any suggestions?
First question(s) I have is what version of PostgreSQL? what hardware is
the commercial SQL server running on? PostgreSQL? memory, cpu, hard
drives, etc?
I run something similar on a 7.0.2 machine with a table consisting of
~9million rows and another of ~100k, and results come back in <1min ... in
fact, the exact query I've tried looks something like:
SELECT url_id,intag
FROM ndict
WHERE word_id=-903076670
AND url_id IN ( SELECT distinct url_id
FROM ndict
WHERE word_id=-70722333
AND url_id IN ( SELECT distinct url_id
FROM ndict,url
WHERE ndict.word_id=572517542
AND url.rec_id=ndict.url_id
AND ((url.url || '') LIKE 'http://www.postgresql.org/%';) ) );
with subsequent run results looking like:
0.000u 0.014s 0:24.12 0.0% 768+1116k 0+0io 0pf+0w
%time psql -h db -o tmpfile udmsearch < query
0.006u 0.006s 0:08.56 0.0% 0+0k 1+0io 0pf+0w
%time psql -h db -o tmpfile udmsearch < query
0.005u 0.000s 0:09.10 0.0% 0+0k 0+0io 0pf+0w
%time psql -h db -o tmpfile udmsearch < query
0.000u 0.012s 0:11.07 0.0% 300+540k 0+0io 0pf+0w
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Speed or configuration
@ 2000-08-20 18:01 Tom Lane <tgl@sss.pgh.pa.us>
parent: Franz J Fortuny <ffortuny@ivsol.com>
2 siblings, 1 reply; 9+ messages in thread
From: Tom Lane @ 2000-08-20 18:01 UTC (permalink / raw)
To: Franz J Fortuny <ffortuny@ivsol.com>; +Cc: pgsql-sql
"Franz J Fortuny" <ffortuny@ivsol.com> writes:
> [ this query is slow: ]
> select xx1,xx2,xx3 from tableX
> where field1 in
> (select field1 from tableY where
> field2=NNN and field3=NNN2 and field4=NNN4)
What version of Postgres are you using, and what does EXPLAIN show
as the query plan for this query? How many tableY rows is the sub-
query likely to produce, and how many matches do you expect to get
from tableX?
The Postgres planner is not currently very smart about optimizing
sub-SELECTs. We hope to do something about that by 7.2 or so,
but in the meantime you should look at transforming the query to
some other form. You might find that EXISTS will help:
select xx1,xx2,xx3 from tableX
where exists (select 1 from tableY where
field1=tableX.field1 and field2=NNN and field3=NNN3 and field4=NNN4)
However this is unlikely to help much unless you change the index
structure for tableY. Perhaps you could write it as a join:
select xx1,xx2,xx3 from tableX, tableY
where tableX.field1=tableY.field1 and field2=NNN
and field3=NNN3 and field4=NNN4
although this won't give quite the same results if there can be multiple
matching rows in tableY for a tableX row. (DISTINCT might help if so.)
regards, tom lane
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Speed or configuration
@ 2000-08-20 18:11 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Franz J Fortuny <ffortuny@ivsol.com>
2 siblings, 0 replies; 9+ messages in thread
From: Stephan Szabo @ 2000-08-20 18:11 UTC (permalink / raw)
To: Franz J Fortuny <ffortuny@ivsol.com>; +Cc: pgsql-sql
(It won't really be forever, just probably
a really long time)
You can usually get around it by rewriting the
query to use EXISTS rather than IN.
Stephan Szabo
sszabo@bigpanda.com
On Sun, 20 Aug 2000, Franz J Fortuny wrote:
> At our company we are presently using a commercial
> database that generates results from this query:
>
> select xx1,xx2,xx3 from tableX
> where field1 in
> (select field1 from tableY where
> field2=NNN and field3=NNN2 and field4=NNN4)
>
> tableX has 790,000 rows, and an index on field1
> tableY has abou 175,000 rows and an index that includes
> field2,field3,field4 (and 2 other fields not being used
> here)
>
> Of course, the order in the indexes is the logical one.
>
> I have made copies of the tables from the commercial SQL
> server to PostgreSQL and PostgreSQL is consistently
> faster in things like count(*) and certain other
> queries.
>
> But when it comes to the above mentioned query,
> PostgreSQL simply stays there, forever. The postgres
> backend must be killed in order to free the client
> program.
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Speed or configuration
@ 2000-08-20 21:50 Franz J Fortuny <ffortuny@ivsol.com>
parent: The Hermit Hacker <scrappy@hub.org>
0 siblings, 0 replies; 9+ messages in thread
From: Franz J Fortuny @ 2000-08-20 21:50 UTC (permalink / raw)
To: The Hermit Hacker <scrappy@hub.org>; +Cc: pgsql-sql
"First question(s) I have is what version of PostgreSQL?
what hardware is
the commercial SQL server running on? PostgreSQL?
memory, cpu, hard
drives, etc?"
Version: postgresql-7.0.2-2.i386.rpm
Hardware: Same Hardware for all SQL Servers (same
machine, of course, one SQL Server is idle while the
other one is active).
Memory: 512 MB RAM, 16GB Hard disk. Pentium II (old)
I am glad to hear that it's possible to get such good
results. I always thought there would be a way. I'll
try this query you have suggested...
Franz J Fortuny
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Speed or configuration
@ 2000-08-20 22:09 Franz J Fortuny <ffortuny@ivsol.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 9+ messages in thread
From: Franz J Fortuny @ 2000-08-20 22:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
"What version of Postgres are you using, and what does
EXPLAIN show
as the query plan for this query? How many tableY rows
is the sub-
query likely to produce, and how many matches do you
expect to get
from tableX?"
Version: postgresql-7.0.2-2.i386.rpm
Explain: Scan table, scan table. (Plus the costs, etc.)
About 9,000 matches from tableX are expected, and a lot
less from
tableY (about 2000, at the most).
The index structure of tableY includes 5 fields. Most of
the time, we need only the first and second fields. Less
often, up to the third field and a lot less often the
other 2 left fields (in the "where" clauses).
However, field1 of tableX references field1 of tableY.
If I use a program to get the results, they come out
incredibly fast (faster in postgreSQL than with the
commercial program)
(A select to get a first record set from tableY, then
navigate the rows, executing a select for tableX for
each row...)
These the structures of the tables:
create table tableX
(
col0 integer not null primary key,
col1 integer not null,
col2 integer not null,
col3 char(20),
col4 char(8),
col5 char(8),
unique(col1,col2,col3,col4,col5)
);
create table tableY
(
col0 integer not null references tableX(col0),
col1 integer,
col2 integer,
col3 float,
col4 float
);
The slow query is:
select col1,col2,sum(col3),sum(col4)
from tableY
where col0 in
(select col0 from tableX where col1=:col1 and
col2=:col2)
group by col1,col2;
Explain plan shows ALWAYS scan table, scan table, scan
table.
tableY contains about 900,000 rows, and tableX about
65,000.
Any more light about the slowness?
Franz J Fortuny
^ permalink raw reply [nested|flat] 9+ messages in thread
* RE: Speed or configuration
@ 2000-08-20 22:49 The Hermit Hacker <scrappy@hub.org>
parent: Franz J Fortuny <ffortuny@ivsol.com>
0 siblings, 0 replies; 9+ messages in thread
From: The Hermit Hacker @ 2000-08-20 22:49 UTC (permalink / raw)
To: Franz J Fortuny <ffortuny@ivsol.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql
On Sun, 20 Aug 2000, Franz J Fortuny wrote:
>
>
>
> "What version of Postgres are you using, and what does
> EXPLAIN show
> as the query plan for this query? How many tableY rows
> is the sub-
> query likely to produce, and how many matches do you
> expect to get
> from tableX?"
>
> Version: postgresql-7.0.2-2.i386.rpm
> Explain: Scan table, scan table. (Plus the costs, etc.)
use cut-n-paste please, and send us the results of the EXPLAIN ... stuff
like the cost estimates and whatnot tell us *so* much ...
^ permalink raw reply [nested|flat] 9+ messages in thread
* Speed or configuration
@ 2000-08-21 17:53 Franz J Fortuny <ffortuny@ivsol.com>
0 siblings, 1 reply; 9+ messages in thread
From: Franz J Fortuny @ 2000-08-21 17:53 UTC (permalink / raw)
To: pgsql-sql
The Hermit Hacker wrote:
"use cut-n-paste please, and send us the results of the
EXPLAIN ... stuff
like the cost estimates and whatnot tell us *so* much
..."
This is it:
exis=# \d pvdprcod
NUMART | integer | not null
NUMDEP | smallint | not null
NUMPRO | smallint | not null
MODELO | varchar(20) | not null
TALLA | varchar(4) | not null
COLOR | varchar(3) | not null
exis=# \d venart
cvetda | smallint | not null
numdep | smallint | not null
numart | integer | not null
mes | smallint | not null
anio | integer | not null
tipotr | varchar(2) |
importe | float8 |
cantidad | float8 |
exis=# explain select
cvetda,anio,mes,sum(importe),sum(cantidad)
exis-# from venart where numart in
exis-# (select "NUMART" from pvdprcod where "NUMDEP"=7
and "NUMPRO"=108)
exis-# group by cvetda,numdep,anio,mes;
NOTICE: QUERY PLAN:
Aggregate (cost=79015875401357.48..79015875413208.91
rows=79010 width=26)
-> Group (cost=79015875401357.48..79015875409258.44
rows=790095 width=26)
-> Sort
(cost=79015875401357.48..79015875401357.48 rows=790095
width=26)
-> Seq Scan on venart
(cost=100000000.00..79015875283591.09 rows=790095
width=26)
SubPlan
-> Materialize
(cost=100007942.42..100007942.42 rows=34 width=4)
-> Seq Scan on pvdprcod
(cost=100000000.00..100007942.42 rows=34 width=4)
EXPLAIN
Why Seq Scan if indexes have been created on the
columns used for access?
Best regards,
Franz J Fortuny
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Speed or configuration
@ 2000-08-23 16:42 Franz J Fortuny <ffortuny@ivsol.com>
parent: Franz J Fortuny <ffortuny@ivsol.com>
0 siblings, 0 replies; 9+ messages in thread
From: Franz J Fortuny @ 2000-08-23 16:42 UTC (permalink / raw)
To: pgsql-sql
Any light on this subject?
The Hermit Hacker wrote:
"use cut-n-paste please, and send us the results of the
EXPLAIN ... stuff
like the cost estimates and whatnot tell us *so* much
..."
This is it:
exis=# \d pvdprcod
NUMART | integer | not null
NUMDEP | smallint | not null
NUMPRO | smallint | not null
MODELO | varchar(20) | not null
TALLA | varchar(4) | not null
COLOR | varchar(3) | not null
exis=# \d venart
cvetda | smallint | not null
numdep | smallint | not null
numart | integer | not null
mes | smallint | not null
anio | integer | not null
tipotr | varchar(2) |
importe | float8 |
cantidad | float8 |
exis=# explain select
cvetda,anio,mes,sum(importe),sum(cantidad)
exis-# from venart where numart in
exis-# (select "NUMART" from pvdprcod where "NUMDEP"=7
and "NUMPRO"=108)
exis-# group by cvetda,numdep,anio,mes;
NOTICE: QUERY PLAN:
Aggregate (cost=79015875401357.48..79015875413208.91
rows=79010 width=26)
-> Group (cost=79015875401357.48..79015875409258.44
rows=790095 width=26)
-> Sort
(cost=79015875401357.48..79015875401357.48 rows=790095
width=26)
-> Seq Scan on venart
(cost=100000000.00..79015875283591.09 rows=790095
width=26)
SubPlan
-> Materialize
(cost=100007942.42..100007942.42 rows=34 width=4)
-> Seq Scan on pvdprcod
(cost=100000000.00..100007942.42 rows=34 width=4)
EXPLAIN
Why Seq Scan if indexes have been created on the
columns used for access?
Best regards,
Franz J Fortuny
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2000-08-23 16:42 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-08-20 16:05 Speed or configuration Franz J Fortuny <ffortuny@ivsol.com>
2000-08-20 17:59 ` The Hermit Hacker <scrappy@hub.org>
2000-08-20 21:50 ` Franz J Fortuny <ffortuny@ivsol.com>
2000-08-20 18:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2000-08-20 22:09 ` Franz J Fortuny <ffortuny@ivsol.com>
2000-08-20 22:49 ` The Hermit Hacker <scrappy@hub.org>
2000-08-20 18:11 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-08-21 17:53 Speed or configuration Franz J Fortuny <ffortuny@ivsol.com>
2000-08-23 16:42 ` Franz J Fortuny <ffortuny@ivsol.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox