pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Arian Prins <prinsarian@zonnet.nl>
To: pgsql-sql@postgresql.org
Subject: Re: need some help understanding sloq query
Date: Tue, 04 Dec 2001 09:31:30 +0100
Message-ID: <3C0C89E2.EC2D9325@zonnet.nl> (raw)
References: <01112918455100.01788@McIntosh>

Hello Esger,

Esger Abbink schreef:

> i have a little performance problem.
>
> db (simplified):
>
> table current:
> current_update_id, ...
>
> table datasets:
> set_id, update_id, ....
>
> table ents:
> e_id, set_id, ...
>
> table qtys:
> set_id, e_id, ...
>
> indexes are defined on all set_id's & datasets.update_id.
>
> an update consists of several sets which in turn consists of several ents, for
> a specific ent in a set multiple qtys may exist.
> (normal: 1 update - 1 set - few hundred ents - 1 qty per ent)
>
> now i want to do the following: i want to get some specific qty values for the
> ents of the last update only.
>
> so i do a query like:
>
> select some_other_fields from ents e, qtys q where e.set_id = q.set_id and
> e.e_id = q.e_id and e.set_id in (select set_id from datasets
> where update_id in (select cur_update_id from current)) and
> q.other_field = some_const ;

[SNIP]

If I understand correctly, the table current allways contains 1 row, namely the
current_update_id, identifying the update that you want to query on. That would mean you could
start out from this table and Inner Join all your tables... this way your result-set would be
all data for current_update_id:

select some_other_fields
from ents e,
     qtys q,
     datasets s,
     current c
where e.set_id = q.set_id
and   e.e_id = q.e_id
and   s.set_id = e.set_id
and   s.update_id = c.current_update_id
and   q.other_field = some_const;

If you used primary keys and indexes this query should use indexes allways.
Other ideas:

select some_other_fields
from  ents e,
         qtys q
where e.set_id = q.set_id
and   e.e_id = q.e_id
and   e.set_id in (select set_id
                   from datasets
                   where update_id = (select max(current_update_id) from current))

Given your first example and queryplan, I think the problem is in the Mergejoin; both from
qtys and from ents an enormous amount of data is selected that is only narowed down in the
last step, the merge-join.

I think using my first query, the optimizer will be much more efficient. I couldn't reproduce
your query to test though... too much  vagueness.... If this doesn't help then post some
sql-definitions of your database.

Succes,
A. Prins.




view thread (5+ messages)  latest in thread

Message-ID: <3C0C89E2.EC2D9325@zonnet.nl>
Permalink:  ../3C0C89E2.EC2D9325@zonnet.nl/
Also on:    postgresql.org/message-id/3C0C89E2.EC2D9325@zonnet.nl

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: prinsarian@zonnet.nl
  Subject: Re: need some help understanding sloq query
  In-Reply-To: <3C0C89E2.EC2D9325@zonnet.nl>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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