4

Let's say I have the following hypothetical data structure:

create table "country"
(
  country_id integer,  
  country_name varchar(50),
  continent varchar(50),
  constraint country_pkey primary key (country_id)
);

create table "person"
(
  person_id integer,
  person_name varchar(100),
  country_id integer,
  constraint person_pkey primary key (person_id)
);

create table "event"
(
  event_id integer,
  event_desc varchar(100),
  country_id integer,
  constraint event_pkey primary key (event_id)
);

I want to query the number of rows of people and events per country. I decided to use a subquery. 

select c.country_name, sum(sub1.person_count) as person_count, sum(sub2.event_count) as event_count
from
  "country" c
  left join (select country_id, count(*) as person_count from "person" group by country_id) sub1
    on (c.country_id=sub1.country_id)
  left join (select country_id, count(*) as event_count from "event" group by country_id) sub2
    on (c.country_id=sub2.country_id)
group by c.country_name

I know you can do this by using select statements in the fields list, but the advantage of using subqueries is that I am more flexible in changing the SQL to make it summarized and use another field. Let's say if I change the query to display it by continent, it will be as simple as replacing the field "c.country_name" into "c.continent".

My problem is regarding filtering. If we add a where clause like so:

select c.country_name, 
  sum(sub1.person_count) as person_count, 
  sum(sub2.event_count) as event_count
from
  "country" c
  left join (select country_id, count(*) as person_count from "person" group by country_id) sub1
    on (c.country_id=sub1.country_id)
  left join (select country_id, count(*) as event_count from "event" group by country_id) sub2
    on (c.country_id=sub2.country_id)
where c.country_name='UNITED STATES'
group by c.country_name

The subqueries seem to still execute the counting for all countries. Assume that the person and event tables are huge and I already have indexes on country_id on all tables. It's really slow. Shouldn't the database only execute the subqueries for the country that was filtered? Do i have to re-create the country filter to each subquery (this is very tedious and code is not easily modifiable)? I am using both PostgreSQL 8.3 and 9.0 by the way but I guess the same happens in other databases.

clj
  • 53
  • 1
  • 7
  • What does the explain plan look like? – Kuberchaun Oct 28 '11 at 03:52
  • This is only a hypothetical scenario. But, on a production db running a very similar query, it did a sequential scan on the tables in the subquery. It never used the index. And these tables were big. – clj Oct 28 '11 at 05:21

2 Answers2

2

Shouldn't the database only execute the subqueries for the country that was filtered?

No. The first step in a query like yours is to appear to build a working table from all of the table constructors in the FROM clause. The WHERE clause is evaluated after that.

Imagine how you'd do this if sub1 and sub2 were both base tables instead of subselects. They'd both have two columns, and they'd both have one row for each country_id. And if you wanted to JOIN all the rows, you'd write it like this.

from
  "country" c
  left join sub1 on (c.country_id=sub1.country_id)
  left join sub2 on (c.country_id=sub2.country_id)

But if you wanted to JOIN on a single row, you'd write something equivalent to this.

from
  "country" c
  left join (select * from sub1 where country_id = ?)
    on (c.country_id=sub1.country_id)
  left join (select * from sub2 where country_id = ?)
    on (c.country_id=sub2.country_id)

Joe Celko, who helped develop early SQL standards, has often written about how SQL's order of evaluation appears on Usenet.

Mike Sherrill 'Cat Recall'
  • 91,602
  • 17
  • 122
  • 185
  • But, wouldn't current database systems do optimizations in this case? How do you suggest I modify the query so that it is still both flexible/reusable/easily modifiable and still perform fast? I don't think it's practical to try to re-create the filter to each subquery. – clj Oct 28 '11 at 05:23
  • Sure. All dbms will optimize where they can. But you're clearly telling the dbms you want sub1 to be a table of two columns (country_id, person_count), and that you want it to have one row for each country_id. Same reasoning for sub2. – Mike Sherrill 'Cat Recall' Oct 28 '11 at 10:57
  • Too bad we don't have a good solution to this. This makes the SQL code a lot more difficult to maintain/adapt to changing filters while still performing fast. – clj Nov 02 '11 at 05:22
  • At the conceptual level, your original query would be named along the lines of "count person events for one country". Its name and the names of its columns are part of the database's public interface. The WHERE clauses implement the "for one country" part. You could write a PostgreSQL function that takes a country name as a parameter, and substitutes that parameter in all the necessary WHERE clauses. Any more complex filtering--say by population, or event, or person's gender, or addresses--doesn't make sense in a dbms object named "count person events for one country". – Mike Sherrill 'Cat Recall' Nov 02 '11 at 10:18
  • The advantage of the query... select from "country" c left join (select country_id, count(*) as person_count from "person" group by country_id) sub1 on (c.country_id=sub1.country_id) left join (select country_id, count(*) as event_count from "event" group by country_id) sub2 on (c.country_id=sub2.country_id) group by ...is that I can easily change the field list and the grouping list to make it by continent or by specific country without changing the from clause. It makes it easier to adapt/maintain SQL code. – clj Nov 03 '11 at 10:44
  • Since the from clause is centered on the country table, anything related to country can be queried. So, for example, it would be very easy to count persons and events by continent or using any other field on the country table or we can even add joins to other tables related by country. Of course it has its limits as to what info you can get, but the point is it is a lot more flexible/maintainable code-wise because the from clause can be fixed and have only the select and group by clause change rather than having to cascade/rewrite the where clause filters down to each subquery. – clj Nov 03 '11 at 10:51
0
  • Can you filtering/grouping rows using country_id not country_name? I suppose you do not have index on name.
  • Subqueries do not use any index on it is ok because you scan all table. If you want to reduce scans you should filter data.
ravnur
  • 2,772
  • 19
  • 28
  • I have an index on country_name. But, still the subqueries scan the entire table and not just the specific country filtered. – clj Oct 28 '11 at 08:34
  • Of course they do. Because you do not filter data. Index used only when optimizer think that you retrieve small amount of rows (using index to do fast scans). When you are scan whole table it is faster to run thru whole table to get all records. So... optimizer is fine and you should read docs about indexing strategies :) – ravnur Oct 28 '11 at 09:12
  • I have the "where c.country_name='UNITED STATES'" filter. So, why do the subqueries still scan the whole table? – clj Oct 28 '11 at 09:20
  • because of optimizer, because it works in this way. It starts to analyze query from bottom to top. So in your case it will run first of all two sub-query and get two sub-sets. Then it will join it `country` table and filter data. Please do explain for this query and you will see it. – ravnur Oct 28 '11 at 09:26
  • Is there a anything I can do to avoid putting the filter inside the subquery? Let's say I want to make this query into a view. That would mean using this view would be very slow if you filter a specific country. It would not be possible to make this view run fast if you filter a specific country because that would mean you need to modify the inner subqueries to also have your filter. – clj Oct 28 '11 at 09:32
  • You can run sub-queries separately: 1) get person_count 2) get event_count. IMHO you are trying to reinvent bike – ravnur Oct 28 '11 at 09:39