Hello everyone,
We've recently finished developing a bigger webapplication, and we are
about to put it online.
I ran some load tests yesterday, and configured 'slow query' logging
beforehand, so I could see if there might be a performance bottleneck
in the PG. While I discovered no real problems, the log file analysis
using pgFouine revealed two queries, which are executed often, and
take quite a bit some time.
I'm just curious if there is any way to improve the performance of
those queries. I'm seeing SeqScans in the EXPLAIN ANALYZE, but nothing
I have done yet has removed those.
The statements and query plans are:
---- Query 1 -----
explain analyze SELECT
n.name_short,n.flag,n.nation_id,n.urlidentifier,count(p.person_id) as
athletes from nations n left join persons p on n.nation_id =
p.nation_id left join efclicences e on p.person_id = e.person_id where
continent = 'eu' and p.deleted = false and p.inactive = false and
e.fencer = true group by
n.name_short,n.flag,n.nation_id,n.urlidentifier order by n.name_short;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=9997.21..9997.32 rows=44 width=33) (actual
time=872.000..872.000 rows=44 loops=1)
Sort Key: n.name_short
Sort Method: quicksort Memory: 28kB
-> HashAggregate (cost=9995.45..9996.01 rows=44 width=33) (actual
time=872.000..872.000 rows=44 loops=1)
-> Hash Join (cost=5669.49..9611.83 rows=30690 width=33)
(actual time=332.000..720.000 rows=142240 loops=1)
Hash Cond: (e.person_id = p.person_id)
-> Seq Scan on efclicences e (cost=0.00..2917.29
rows=143629 width=8) (actual time=0.000..80.000 rows=143629 loops=1)
Filter: fencer
-> Hash (cost=5285.87..5285.87 rows=30690 width=33)
(actual time=332.000..332.000 rows=142240 loops=1)
-> Hash Join (cost=7.10..5285.87 rows=30690
width=33) (actual time=0.000..256.000 rows=142240 loops=1)
Hash Cond: (p.nation_id = n.nation_id)
-> Seq Scan on persons p
(cost=0.00..4438.29 rows=142288 width=16) (actual time=0.000..112.000
rows=142418 loops=1)
Filter: ((NOT deleted) AND (NOT inactive))
-> Hash (cost=6.55..6.55 rows=44
width=25) (actual time=0.000..0.000 rows=44 loops=1)
-> Seq Scan on nations n
(cost=0.00..6.55 rows=44 width=25) (actual time=0.000..0.000 rows=44
loops=1)
Filter: ((continent)::text = 'eu'::text)
Total runtime: 880.000 ms
(17 rows)
--- Query 2 ---
explain analyze SELECT persons.person_id AS persons_person_id FROM
persons LEFT OUTER JOIN indexing_persons ON persons.person_id =
indexing_persons.person_id WHERE indexing_persons.person_id IS NULL
OR persons.modified > indexing_persons.indexed ORDER BY
persons.modified DESC LIMIT 1000;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=17755.23..17757.73 rows=1000 width=16) (actual
time=372.000..372.000 rows=0 loops=1)
-> Sort (cost=17755.23..17994.61 rows=95753 width=16) (actual
time=372.000..372.000 rows=0 loops=1)
Sort Key: persons.modified
Sort Method: quicksort Memory: 25kB
-> Hash Left Join (cost=4313.44..12505.20 rows=95753
width=16) (actual time=372.000..372.000 rows=0 loops=1)
Hash Cond: (persons.person_id = indexing_persons.person_id)
Filter: ((indexing_persons.person_id IS NULL) OR
(persons.modified > indexing_persons.indexed))
-> Seq Scan on persons (cost=0.00..4438.29
rows=143629 width=16) (actual time=0.000..56.000 rows=143629 loops=1)
-> Hash (cost=2534.86..2534.86 rows=142286 width=16)
(actual time=140.000..140.000 rows=143629 loops=1)
-> Seq Scan on indexing_persons
(cost=0.00..2534.86 rows=142286 width=16) (actual time=0.000..72.000
rows=143629 loops=1)
Total runtime: 372.000 ms
(11 rows)
---- Table definitions ---
\d persons
Table "public.persons"
Column | Type |
Modifiers
---------------------+--------------------------+-------------------------------------------------------------
person_id | bigint | not null default
nextval('persons_person_id_seq'::regclass)
givenname | character varying(100) | not null
surname | character varying(100) | not null
name_display_short | character varying(20) | not null
name_display_long | character varying(50) | not null
title | character varying(50) |
postnominals | character varying(10) |
gender | character varying(1) |
dateofbirth | date |
nation_id | bigint |
club_id | bigint |
handed | character varying(1) |
comment | text |
national_identifier | character varying(50) |
fie_identifier | character varying(50) |
honorary_member_efc | boolean | not null
honorary_member_fie | boolean | not null
created | timestamp with time zone | not null
modified | timestamp with time zone | not null
dead | boolean | not null
inactive | boolean | not null
deleted | boolean | not null
urlidentifier | character varying(50) | not null
profilepicture | bigint |
ophardt_identifier | bigint |
idtoken | character varying(10) |
consolidated | bigint |
Indexes:
"persons_pkey" PRIMARY KEY, btree (person_id)
"persons_urlidentifier_key" UNIQUE, btree (urlidentifier)
"idx_persons_deleted" btree (deleted)
"idx_persons_inactive" btree (inactive)
"idx_persons_inactive_deleted" btree (inactive, deleted)
Foreign-key constraints:
"persons_club_id_fkey" FOREIGN KEY (club_id) REFERENCES
clubs(club_id) ON UPDATE CASCADE ON DELETE SET NULL
"persons_consolidated_fkey" FOREIGN KEY (consolidated) REFERENCES
persons(person_id) ON UPDATE CASCADE ON DELETE CASCADE
"persons_nation_id_fkey" FOREIGN KEY (nation_id) REFERENCES
nations(nation_id)
Triggers:
persons_modified BEFORE UPDATE ON persons FOR EACH ROW EXECUTE
PROCEDURE setmodified()
\d nations
Table "public.nations"
Column | Type |
Modifiers
-------------------+--------------------------+-------------------------------------------------------------
nation_id | bigint | not null default
nextval('nations_nation_id_seq'::regclass)
code | character varying(3) | not null
name_short | character varying(100) | not null
name_official | character varying(200) | not null
name_official_en | character varying(200) | not null
website | character varying(255) |
flag | character varying(255) |
comment | text |
geocode_longitude | double precision |
geocode_latitude | double precision |
geocode_zoom | double precision |
created | timestamp with time zone | not null
modified | timestamp with time zone | not null
inactive | boolean | not null default false
deleted | boolean | not null default false
efc | boolean | not null
subname | character varying(255) |
street | character varying(255) |
postcode | character varying(255) |
city | character varying(255) |
country | character varying(255) |
fax | character varying(255) |
mobile | character varying(255) |
phone | character varying(255) |
email | character varying(255) |
urlidentifier | character varying(50) | not null
continent | character varying(2) | not null default
'eu'::character varying
logo_p2picture_id | bigint |
idtoken | character varying(10) |
Indexes:
"nations_pkey" PRIMARY KEY, btree (nation_id)
Foreign-key constraints:
"nations_logo_p2picture_id_fkey" FOREIGN KEY (logo_p2picture_id)
REFERENCES p2picture(picture_id) ON UPDATE CASCADE ON DELETE CASCADE
\d efclicences
Table "public.efclicences"
Column | Type |
Modifiers
---------------+--------------------------+---------------------------------------------------------------------
efclicence_id | bigint | not null default
nextval('efclicences_efclicence_id_seq'::regclass)
person_id | bigint | not null
valid_from | date | not null
valid_to | date |
created | timestamp with time zone | not null
modified | timestamp with time zone | not null
inactive | boolean | not null
fencer | boolean | not null
official | boolean | not null
referee | boolean | not null
member | boolean | not null
Indexes:
"efclicences_pkey" PRIMARY KEY, btree (efclicence_id)
Foreign-key constraints:
"efclicences_person_id_fkey" FOREIGN KEY (person_id) REFERENCES
persons(person_id) ON UPDATE CASCADE ON DELETE CASCADE
\d indexing_persons
Table "public.indexing_persons"
Column | Type | Modifiers
-----------+--------------------------+----------------------------------------------------------------------
person_id | bigint | not null default
nextval('indexing_persons_person_id_seq'::regclass)
indexed | timestamp with time zone |
Indexes:
"indexing_persons_pkey" PRIMARY KEY, btree (person_id)
Foreign-key constraints:
"indexing_persons_person_id_fkey" FOREIGN KEY (person_id)
REFERENCES persons(person_id) ON DELETE CASCADE
--- Additional info ---
These are mostly stock table definitions, and not much has done yet to
improve performance there.
Autovacuuming is turned on for the PG, I have increased the available
memory a bit (as the db server as 4 GB of RAM), and added logging
options to the stock Debian configuration, but nothing more.
The system in a XEN vServer running on 4 Cores, with those said 4 GB of RAM.
It is nothing deal breaking at the moment, the performance of those
queries, as we don't have a problem at the moment, but I'm curious to
learn more about query optimization, to maybe be able to analyze and
correct problems in the future myself, so any help and remarks are
greatly appreciated.
Thanks in advance!
Jens