[SQL] Query optimisation

[Michel Vrand]

Hi!
I first apologize for my poor english.

We are working with linux-apache-postgresql-php3.
One among our queries is structured as follow :

$result = pg_exec ($conn, "SELECT produits.id_produit, produits.id_proprio,
articles.categ, groupes.nom, produits.pvttc_prod, articles.titre,
articles.type_1, articles.obs_art
FROM produits, articles, groupes
WHERE $conditions  $relations;");

$conditions may be

1/ ...AND groupes.nom = '$something' AND
or
2/ ...AND groupes.nom ~* '$something' AND

In the first case, the query is reasonnably fast (0.30 s for 4 items on
15000)
In the second case, the query becomes very slow (more than 31 s for the same
result)

to give example, in the first case $something = "Beatles"
   in the second case $something = "beatl"

How to optimise speed ? I tried to type EXPLAIN but I do not understand the
result :

For 1/
Nested loop (cost=0.00 size=1 width=86)
-> Merge Join (cost=0.00 size=1 width=72)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Seq Scan on produits (cost=0.00 size=0 width=16)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Index Scan using type_1.idx on articles (cost=0.00 size=0
width=56)
-> Index Scan using nom_groupe.idx on groupes (cost=0.00 size=0 width=16)

For 2/
Nested loop (cost=0.00 size=1 width=86)
-> Merge Join (cost=0.00 size=1 width=72)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Seq Scan on produits (cost=0.00 size=0 width=16)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Index Scan using type_1.idx on articles (cost=0.00 size=0
width=56)
-> Seq Scan on groupes (cost=0.00 size=0 width=16)

The only difference seems to be the use (or not) of index in the last line.
Do you think it causes such a decrease of speed ? How to work around ?

Thanks in advance

[SQL] Where Can I find JDBC driver.

[Mateusz Mazur]

Hello.
I wonder where I can find JDBC driver for Postgresql. I think it should be
class (sorry I am newbie). I would be very greatfull for quick response.

Mateusz.

Re: [SQL] Query optimisation

[Richard Huxton]

From: "Michel Vrand" <[EMAIL PROTECTED]>


> $conditions may be
>
> 1/ ...AND groupes.nom = '$something' AND
> or
> 2/ ...AND groupes.nom ~* '$something' AND
>
> In the first case, the query is reasonnably fast (0.30 s for 4 items on
> 15000)
> In the second case, the query becomes very slow (more than 31 s for the
same
> result)
>
> to give example, in the first case $something = "Beatles"
>in the second case $something = "beatl"
>
> How to optimise speed ? I tried to type EXPLAIN but I do not understand
the
> result :
>
You are right - the index *does* make that much difference. The problem is
that ~* matches *anywhere* in the string so it can't use the index - it has
to read each entry.

If you want to match the start of the entry, I know of one way to make the
index work. Use

groupes.nom>='beatl' AND groupes.nom<='beatlz'

And that should work - although 'z' is a poor character to use - pick the
highest valid character in your character set. You might also want to look
in the archives for the thread on pgsql-general with a subject of 'Simple
Question: Case sensitivity'

- Richard Huxton

Re: [SQL] FOREIGN KEY errors.

[Joseph Shraibman]

Script to reproduce the problem.  It drops the tables at the end of the
script.

Stephan Szabo wrote:
> 
> There was a bug (which should be fixed for 7.1) that got the
> arguments wrong for the alter time check of the existing data.
> I think I should be able to get a patch together to fix it once
> I get a copy of the 7.0.3 source.
> 
> Can you send the table schema as well so I can test it out?
> 

Script to reproduce the problem.  It drops the tables at the end of the
script.  If data is not inserted into mtable there is no problem.


create table utable (
uk int not null,
pk int not null,
thetext text,
primary key (uk, pk) 
);  

create table mtable(
id serial not null primary key,
mtext text,
o int,  /* corresponds to uk */
p int  /* corresponds to pk */

);

insert into utable (uk,pk,thetext) values (2,4,'blah 2 4');
insert into utable (uk,pk,thetext) values (2,5,'blah 2 5');

insert into mtable (mtext,o,p) values ('m 2 4',2,4);


alter table mtable add FOREIGN KEY (p,o) REFERENCES utable (pk,uk);

drop sequence mtable_id_seq;
drop table mtable;
drop table utable;



-- 
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio.  http://www.targabot.com

[SQL]

[Peeter Smitt]

Hi
 
Is it somehow possible to join tables from multiple 
databases into one query?
 
Thanks
 
Peeter
 
 

Re: [SQL]

[Joseph Shraibman]

No.

> Peeter Smitt wrote:
> 
> Hi
> 
> Is it somehow possible to join tables from multiple databases into one
> query?
> 
> Thanks
> 
> Peeter
> 
> 

-- 
Joseph Shraibman
[EMAIL PROTECTED]
Increase signal to noise ratio.  http://www.targabot.com

Re: [SQL] Where Can I find JDBC driver.

[Robert B. Easter]

On Monday 11 December 2000 11:45, Mateusz Mazur wrote:
> Hello.
> I wonder where I can find JDBC driver for Postgresql. I think it should be
> class (sorry I am newbie). I would be very greatfull for quick response.
>
> Mateusz.

/usr/src/postgresql-7.0.3/src/interfaces/jdbc

Its with the source.

-- 
 Robert B. Easter  [EMAIL PROTECTED] -
- CompTechNews Message Board   http://www.comptechnews.com/ -
- CompTechServ Tech Services   http://www.comptechserv.com/ -
-- http://www.comptechnews.com/~reaster/ 

Re: [SQL] plpgsql

[Kovacs Zoltan Sandor]

> Hi, there,
> 
> Is there any way to handle exception ( such as cannot insert duplicate key on
> a unique index) in
> plpgsql function?
> 
> I don't want it abort whole transaction instead I want to do something else if
> it happened,
> but I don't want to use a select stmt first to waste the time.
Bad news: there is no such statement in PLPGSQL you like. My usual way to
do this is the same you wrote (SELECT first, if no rows FOUND, do the
INSERT).

Zoltan