Skip site navigation (1) Skip section navigation (2)

intelligence in writing a query ...

From: The Hermit Hacker <scrappy(at)hub(dot)org>
To: <pgsql-hackers(at)postgresql(dot)org>
Cc: <tech-fyi(at)hub(dot)org>
Subject: intelligence in writing a query ...
Date: 2001-05-30 17:55:48
Message-ID: Pine.BSF.4.33.0105301446230.82504-100000@mobile.hub.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom, with all the work you've been doing inside planner and optimizer, has
there been anything done for 7.1.2 to make how a query is written cause
the backend to be more intelligent?

I'm playing with a query that I just don't like, since its taking ~3min to
run ...

It started as:

EXPLAIN SELECT distinct s.gid, s.created, count(i.title) AS images
              FROM status s LEFT JOIN images i ON (s.gid = i.gid AND i.active), personal_data pd, relationship_wanted rw
             WHERE s.active AND s.status != 0
               AND (s.gid = pd.gid AND pd.gender = 0)
               AND (s.gid = rw.gid AND rw.gender = 1 )
               AND ( ( age('now', pd.dob) > '26 years' ) AND ( age('now', pd.dob) < '46 years' ) )
               AND country IN ( 'US' )
          GROUP BY s.gid,s.created
          ORDER BY  images desc;
NOTICE:  QUERY PLAN:

Unique  (cost=2365.87..2365.88 rows=1 width=37)
  ->  Sort  (cost=2365.87..2365.87 rows=1 width=37)
        ->  Aggregate  (cost=2365.86..2365.86 rows=1 width=37)
              ->  Group  (cost=2365.86..2365.86 rows=1 width=37)
                    ->  Sort  (cost=2365.86..2365.86 rows=1 width=37)
                          ->  Nested Loop  (cost=167.62..2365.85 rows=1 width=37)
                                ->  Nested Loop  (cost=0.00..600.30 rows=1 width=8)
                                      ->  Index Scan using personal_data_gender on personal_data pd  (cost=0.00..590.79 rows=4 width=4)
                                      ->  Index Scan using relationship_wanted_gid on relationship_wanted rw  (cost=0.00..2.12 rows=1 width=4)
                                ->  Materialize  (cost=1508.62..1508.62 rows=17128 width=29)
                                      ->  Hash Join  (cost=167.62..1508.62 rows=17128 width=29)
                                            ->  Seq Scan on status s  (cost=0.00..566.24 rows=17128 width=12)
                                            ->  Hash  (cost=149.70..149.70 rows=7170 width=17)
                                                  ->  Seq Scan on images i  (cost=0.00..149.70 rows=7170 width=17)

EXPLAIN

And, after playing a bit, I've got it to:

2EXPLAIN SELECT distinct s.gid, s.created, count(i.title) AS images
              FROM status s LEFT JOIN images i ON (s.gid = i.gid AND i.active), relationship_wanted rw
             WHERE s.active AND s.status != 0
               AND EXISTS ( SELECT gid
                                FROM relationship_wanted
                               WHERE gender = 1 )
               AND EXISTS ( SELECT gid
                                FROM personal_data
                               WHERE gender = 0
                                 AND ( ( age('now', dob) > '26 years' ) AND ( age('now', dob) < '46 years' ) )
                                 AND country IN ( 'US' ) )
          GROUP BY s.gid,s.created
          ORDER BY  images desc;
NOTICE:  QUERY PLAN:

Unique  (cost=313742358.09..314445331.35 rows=9372977 width=29)
  InitPlan
    ->  Seq Scan on relationship_wanted  (cost=0.00..1006.03 rows=1446 width=4)
    ->  Index Scan using personal_data_gender on personal_data  (cost=0.00..590.79 rows=4 width=4)
  ->  Sort  (cost=313742358.09..313742358.09 rows=93729769 width=29)
        ->  Aggregate  (cost=285211774.88..292241507.54 rows=93729769 width=29)
              ->  Group  (cost=285211774.88..289898263.32 rows=937297688 width=29)
                    ->  Sort  (cost=285211774.88..285211774.88 rows=937297688 width=29)
                          ->  Result  (cost=167.62..24262791.77 rows=937297688 width=29)
                                ->  Nested Loop  (cost=167.62..24262791.77 rows=937297688 width=29)
                                      ->  Hash Join  (cost=167.62..1508.62 rows=17128 width=29)
                                            ->  Seq Scan on status s  (cost=0.00..566.24 rows=17128 width=12)
                                            ->  Hash  (cost=149.70..149.70 rows=7170 width=17)
                                                  ->  Seq Scan on images i  (cost=0.00..149.70 rows=7170 width=17)
                                      ->  Seq Scan on relationship_wanted rw  (cost=0.00..869.22 rows=54722 width=0)

EXPLAIN

Not much of an improvement ...

The 'personal_data' EXISTS clause:

SELECT gid
  FROM personal_data
 WHERE gender = 0
   AND ( ( age('now', dob) > '26 years' ) AND ( age('now', dob) < '46 years' ) )
   AND country IN ( 'US' ) ;

NOTICE:  QUERY PLAN:

Index Scan using personal_data_gender on personal_data  (cost=0.00..590.79 rows=4 width=4)

EXPLAIN

returns 1893 rows, while status contains 26260 rows ... status and
personal_data have a 1-to-1 relationship, so out of 26260 rows in status,
*max* I'm ever going to deal with are the 1893 that are found in
personal_data ...

so, what I'd like to do is have the subselect on personal_data used first,
so as to reduce the set of data that the rest of the query will work only
on those 1893 gid's, instead of all 26260 of them ...

Make sense?


Marc G. Fournier                   ICQ#7615664               IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy(at)hub(dot)org           secondary: scrappy(at){freebsd|postgresql}.org


Responses

pgsql-hackers by date

Next:From: Roberto AbaldeDate: 2001-05-30 18:00:53
Subject: Cache for query plans
Previous:From: Don BaccusDate: 2001-05-30 17:48:20
Subject: Re: Support for %TYPE in CREATE FUNCTION

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group