pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPerformance problem, what to do?
5+ messages / 3 participants
[nested] [flat]
* Performance problem, what to do?
@ 2001-12-18 12:34 Archibald Zimonyi <archie@netg.se>
2001-12-18 12:58 ` Re: Performance problem, what to do? Markus Bertheau <twanger@bluetwanger.de>
0 siblings, 1 reply; 5+ messages in thread
From: Archibald Zimonyi @ 2001-12-18 12:34 UTC (permalink / raw)
To: pgsql-sql
Hi there,
My name is Archibald and I am fairly new to PostGreSQL but I've worked
with databases for a few years on and off and I consider myself to be a
fairly good database designer and I know my SQL statements.
I am currently building a database to represent a card game, where one is
supposed to search through the cards in order to find cards you want.
Each card has certain attributes (cost, name, requirements and such). What
I have done is made one table cards which only includes the name and then
"objectified" the database by using tables connected to the cards table.
Once all the data (and we're talking a small amount of data here, the
whole database is 1.4M)) is in I add my two views and run them. Here is
when my problems start. I did nothing to increase performance by using
VACUUM or CLUSTER but I frequently used EXPLAIN to see how much diskaccess
I had (since I felt that my query went to slow). The numbers went down
drastically and now I have an EXPLAIN output that I think looks very very
good, very few rows affected and the cost is very low (started at around
6000 and up and is now down att 200 so it is a big cut).
The EXPLAIN on my view can be found in the attachment.
How come my query takes almost a minute execute?
My problems actually started when I used VACUUM ANALYZE. After that the
query took forever. A friend suggested I recreate the database and run
VACUUM ANALYZE when the data is just added, something that is also
suggested in various docs and books but that didn't help. So I tried to
CLUSTER some of my tables, it worked better but then suddenly all was slow
again.
We're running this database on a 700Mhz PC running Linux with 396M of RAM,
so for a "hobby" database that is fairly much. The harddrive is a faster
one as well.
The database design follows the normalization rules of designing
databases, I have made som odd choices where I have created a table which
could be ignored and instead added as a column in another table but we're
talking about such small amounts of data that that shouldn't be a problem.
If anyone has any idea at all feel free to respond, I am grateful for any
help I can get on this.
Thanks in advance,
Archie
Attachments:
explain.txt (text/plain, 4.7K) download
NOTICE: QUERY PLAN:
Nested Loop (cost=94.60..199.12 rows=1 width=148)
InitPlan
-> Seq Scan on cardtypes (cost=0.00..1.14 rows=1 width=2)
-> Nested Loop (cost=93.17..160.81 rows=1 width=130)
-> Nested Loop (cost=93.17..151.95 rows=1 width=112)
-> Nested Loop (cost=73.59..131.84 rows=1 width=98)
-> Nested Loop (cost=72.49..123.45 rows=1 width=84)
-> Nested Loop (cost=15.67..66.45 rows=1 width=70)
-> Nested Loop (cost=15.67..64.42 rows=1 width=66)
-> Nested Loop (cost=15.67..62.39 rows=1 width=62)
-> Nested Loop (cost=15.67..60.36 rows=1 width=48)
-> Nested Loop (cost=15.67..58.95 rows=1 width=34)
-> Hash Join (cost=15.67..55.83 rows=2 width=20)
-> Nested Loop (cost=0.00..34.54 rows=127 width=18)
-> Seq Scan on cardtypes c (cost=0.00..1.11 rows=11 width=14)
-> Index Scan using cards_cardtypes2_index on cards_cardtypes ct (cost=0.00..3.02 rows=1 width=4)
-> Hash (cost=15.57..15.57 rows=42 width=2)
-> Index Scan using cards_cardtypes2_index on cards_cardtypes (cost=0.00..15.57 rows=42 width=2)
-> Index Scan using cards_pkey on cards c (cost=0.00..2.01 rows=1 width=14)
-> Seq Scan on cards_names (cost=0.00..1.18 rows=18 width=14)
-> Index Scan using cards_cardtexts_index on cards_cardtexts (cost=0.00..2.01 rows=1 width=14)
-> Index Scan using cards_capacities_index on cards_capacities (cost=0.00..2.01 rows=1 width=4)
-> Index Scan using cards_costs_index on cards_costs (cost=0.00..2.01 rows=1 width=4)
-> Subquery Scan t_disciplines (cost=56.82..56.96 rows=3 width=19)
-> Aggregate (cost=56.82..56.96 rows=3 width=19)
-> Group (cost=56.82..56.89 rows=29 width=19)
-> Sort (cost=56.82..56.82 rows=29 width=19)
-> Nested Loop (cost=0.00..56.11 rows=29 width=19)
-> Seq Scan on disciplines d (cost=0.00..1.27 rows=27 width=14)
-> Index Scan using cards_disciplines2_index on cards_disciplines cd (cost=0.00..2.02 rows=1 width=5)
-> Subquery Scan t_titles (cost=1.10..8.26 rows=10 width=18)
-> Hash Join (cost=1.10..8.26 rows=10 width=18)
-> Seq Scan on cards_titles ct (cost=0.00..2.15 rows=115 width=4)
-> Hash (cost=1.08..1.08 rows=8 width=14)
-> Seq Scan on titles t (cost=0.00..1.08 rows=8 width=14)
-> Subquery Scan t_sets (cost=19.58..19.94 rows=7 width=18)
-> Aggregate (cost=19.58..19.94 rows=7 width=18)
-> Group (cost=19.58..19.76 rows=73 width=18)
-> Sort (cost=19.58..19.58 rows=73 width=18)
-> Nested Loop (cost=0.00..17.33 rows=73 width=18)
-> Seq Scan on sets s (cost=0.00..1.08 rows=8 width=14)
-> Index Scan using cards_sets2_index on cards_sets cs (cost=0.00..2.02 rows=1 width=4)
-> Materialize (cost=7.12..7.12 rows=70 width=18)
-> Nested Loop (cost=0.00..7.12 rows=70 width=18)
-> Seq Scan on sects s (cost=0.00..1.03 rows=3 width=14)
-> Index Scan using cards_sects2_index on cards_sects cs (cost=0.00..2.02 rows=1 width=4)
-> Materialize (cost=38.17..38.17 rows=5 width=18)
-> Hash Join (cost=1.43..38.17 rows=5 width=18)
-> Seq Scan on cards_clans cc (cost=0.00..10.14 rows=614 width=4)
-> Hash (cost=1.34..1.34 rows=34 width=14)
-> Seq Scan on clans c (cost=0.00..1.34 rows=34 width=14)
EXPLAIN
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance problem, what to do?
2001-12-18 12:34 Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
@ 2001-12-18 12:58 ` Markus Bertheau <twanger@bluetwanger.de>
2001-12-18 13:32 ` Re: Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
0 siblings, 1 reply; 5+ messages in thread
From: Markus Bertheau @ 2001-12-18 12:58 UTC (permalink / raw)
To: Archibald Zimonyi <archie@netg.se>; +Cc: pgsql-sql
On Tue, 2001-12-18 at 13:34, Archibald Zimonyi wrote:
> How come my query takes almost a minute execute?
Could you post your table structure and the query for the explain?
Markus Bertheau
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance problem, what to do?
2001-12-18 12:34 Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
2001-12-18 12:58 ` Re: Performance problem, what to do? Markus Bertheau <twanger@bluetwanger.de>
@ 2001-12-18 13:32 ` Archibald Zimonyi <archie@netg.se>
2001-12-18 15:36 ` Re: Performance problem, what to do? Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Archibald Zimonyi @ 2001-12-18 13:32 UTC (permalink / raw)
To: pgsql-sql
On demand, I'll post the database structure as well as the SQL query which
creates the slow respons. I'll add them as attachments (do they come at
all?).
Thanks in advance,
Archie
Attachments:
query (text/plain, 4.9K) download
DROP FUNCTION discipline_concat(text, text);
DROP FUNCTION cardtype_concat(text, text);
DROP FUNCTION set_concat(text, text);
DROP FUNCTION discipline_fix(text);
DROP FUNCTION cardtype_fix(text);
DROP FUNCTION set_fix(text);
DROP AGGREGATE cardtype_string text;
DROP AGGREGATE set_string text;
DROP AGGREGATE discipline_string text;
DROP FUNCTION cardname_string(text, text);
DROP FUNCTION cost_string(text, boolean);
DROP FUNCTION column_to_upper(text, boolean);
DROP VIEW crypt;
DROP VIEW library;
CREATE FUNCTION cardtype_concat(text, text)
RETURNS text
AS
'SELECT $1 || \', \' || $2 AS RESULT'
LANGUAGE 'sql';
CREATE FUNCTION cardtype_fix(text)
RETURNS text
AS
'SELECT substring($1 from 2 for char_length($1)) AS RESULT'
LANGUAGE 'sql';
CREATE FUNCTION discipline_concat(text, text)
RETURNS text
AS
'SELECT $1 || \', \' || $2 AS RESULT'
LANGUAGE 'sql';
CREATE FUNCTION discipline_fix(text)
RETURNS text
AS
'SELECT substring($1 from 3 for char_length($1)) AS RESULT'
LANGUAGE 'sql';
CREATE FUNCTION set_concat(text, text)
RETURNS text
AS
'SELECT $1 || \', \' || $2 AS RESULT'
LANGUAGE 'sql';
CREATE FUNCTION set_fix(text)
RETURNS text
AS
'SELECT substring($1 from 3 for char_length($1)) AS RESULT'
LANGUAGE 'sql';
CREATE AGGREGATE discipline_string
(
SFUNC = discipline_concat,
BASETYPE = text,
STYPE = text,
FINALFUNC = discipline_fix,
INITCOND = ''
);
CREATE AGGREGATE cardtype_string
(
SFUNC = cardtype_concat,
BASETYPE = text,
STYPE = text,
FINALFUNC = cardtype_fix,
INITCOND = ''
);
CREATE AGGREGATE set_string
(
SFUNC = set_concat,
BASETYPE = text,
STYPE = text,
FINALFUNC = set_fix,
INITCOND = ''
);
CREATE FUNCTION column_to_upper(text, boolean)
RETURNS text
AS
'DECLARE
discipline ALIAS FOR $1;
superior ALIAS FOR $2;
BEGIN
IF superior THEN
RETURN upper(discipline);
END IF;
RETURN discipline;
END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION cost_string(text, boolean)
RETURNS text
AS
'DECLARE
cost ALIAS FOR $1;
pool ALIAS FOR $2;
BEGIN
IF pool THEN
RETURN cost || '' pool'';
END IF;
RETURN cost || '' blood'';
END;'
LANGUAGE 'plpgsql';
CREATE FUNCTION cardname_string(text, text)
RETURNS text
AS
'DECLARE
cardname ALIAS FOR $1;
alternate ALIAS FOR $2;
BEGIN
IF alternate ISNULL THEN
RETURN cardname;
END IF;
RETURN cardname || ''/'' || alternate;
END;'
LANGUAGE 'plpgsql';
CREATE VIEW crypt
AS
SELECT
t_cards.card_id AS CardID,
cardname_string(c.cardname, cards_names.cardname) AS Cardname,
t_cardtypes.cardtype AS Cardtype,
t_sets.set AS Set,
t_sects.sect AS Sect,
t_clans.clan AS Clan,
cards_costs.cost AS Cost,
cards_capacities.capacity AS Capacity,
t_titles.title AS Title,
t_disciplines.discipline AS Discipline,
cards_cardtexts.cardtext AS Cardtext
FROM (SELECT card_id
FROM cards_cardtypes
WHERE cardtype_id = (SELECT cardtype_id
FROM cardtypes
WHERE cardtype = 'Vampire')) AS t_cards
INNER JOIN
(
SELECT ct.card_id, c.cardtype
FROM cards_cardtypes ct
INNER JOIN cardtypes c USING (cardtype_id)
WHERE c.cardtype_id = ct.cardtype_id
) AS t_cardtypes USING (card_id)
INNER JOIN cards c USING (card_id)
LEFT JOIN cards_names USING (card_id)
INNER JOIN cards_cardtexts USING (card_id)
INNER JOIN cards_capacities USING (card_id)
LEFT JOIN cards_costs USING (card_id)
LEFT JOIN
(
SELECT card_id, discipline_string(column_to_upper(d.discipline_abbr, cd.superior)) AS discipline
FROM cards_disciplines cd
INNER JOIN disciplines d USING (discipline_id)
WHERE cd.discipline_id = d.discipline_id
GROUP BY card_id
) AS t_disciplines USING (card_id)
LEFT JOIN
(
SELECT ct.card_id, t.title
FROM cards_titles ct
INNER JOIN titles t USING (title_id)
WHERE ct.title_id = t.title_id
) AS t_titles USING (card_id)
INNER JOIN
(
SELECT cs.card_id, set_string(s.set_abbr) AS set
FROM cards_sets cs
INNER JOIN sets s USING (set_id)
WHERE cs.set_id = s.set_id
GROUP BY card_id
) AS t_sets USING (card_id)
INNER JOIN
(
SELECT cs.card_id, s.sect
FROM cards_sects cs
INNER JOIN sects s USING (sect_id)
WHERE cs.sect_id = s.sect_id
) AS t_sects USING (card_id)
INNER JOIN
(
SELECT cc.card_id, c.clan
FROM cards_clans cc
INNER JOIN clans c USING (clan_id)
WHERE cc.clan_id = c.clan_id
) AS t_clans USING (card_id);
tables (text/plain, 9.8K) download
/*
* Removing both the sequence and tables to remake the database
*/
DROP INDEX cards_artists_index;
DROP INDEX cards_capacities_index;
DROP INDEX cards_cardtexts_index;
DROP INDEX cards_cardtypes_index;
DROP INDEX cards_clans_index;
DROP INDEX cards_costs_index;
DROP INDEX cards_disciplines_index;
DROP INDEX cards_names_index;
DROP INDEX cards_rarities_index;
DROP INDEX cards_sects_index;
DROP INDEX cards_sets_index;
DROP INDEX cards_titles_index;
DROP INDEX decks_cards_index;
DROP INDEX logins_cards_index;
DROP INDEX tradelist_index;
DROP INDEX cards_artists2_index;
DROP INDEX cards_cardtypes2_index;
DROP INDEX cards_clans2_index;
DROP INDEX cards_disciplines2_index;
DROP INDEX cards_rarities2_index;
DROP INDEX cards_sects2_index;
DROP INDEX cards_sets2_index;
DROP INDEX cards_titles2_index;
DROP INDEX decks_cards2_index;
DROP INDEX logins_cards2_index;
DROP INDEX tradelist2_index;
DROP SEQUENCE titles_seq;
DROP SEQUENCE clans_seq;
DROP SEQUENCE artists_seq;
DROP SEQUENCE disciplines_seq;
DROP SEQUENCE cards_seq;
DROP SEQUENCE cardtypes_seq;
DROP SEQUENCE logins_seq;
DROP SEQUENCE decks_seq;
DROP SEQUENCE sets_seq;
DROP SEQUENCE rarities_seq;
DROP SEQUENCE sects_seq;
DROP TABLE tradelist;
DROP TABLE decks_cards;
DROP TABLE decks;
DROP TABLE logins_cards;
DROP TABLE logins;
DROP TABLE cards_cardtexts;
DROP TABLE cards_sects;
DROP TABLE cards_titles;
DROP TABLE cards_clans;
DROP TABLE cards_disciplines;
DROP TABLE cards_capacities;
DROP TABLE cards_cardtypes;
DROP TABLE cards_artists;
DROP TABLE cards_sets;
DROP TABLE cards_rarities;
DROP TABLE cards_names;
DROP TABLE cards_costs;
DROP TABLE cards;
DROP TABLE clans;
DROP TABLE titles;
DROP TABLE cardtypes;
DROP TABLE artists;
DROP TABLE disciplines;
DROP TABLE rarities;
DROP TABLE sets;
DROP TABLE sects;
/*
* Sequences for the different PRIMARY KEYS in the tables
*/
CREATE SEQUENCE titles_seq;
CREATE SEQUENCE clans_seq;
CREATE SEQUENCE artists_seq;
CREATE SEQUENCE disciplines_seq;
CREATE SEQUENCE cards_seq;
CREATE SEQUENCE cardtypes_seq;
CREATE SEQUENCE logins_seq;
CREATE SEQUENCE decks_seq;
CREATE SEQUENCE sets_seq;
CREATE SEQUENCE rarities_seq;
CREATE SEQUENCE sects_seq;
/*
* Table depicting the names of the different sects that make up the
* vampire world
*/
CREATE TABLE sects
(
sect_id int2 DEFAULT NEXTVAL('sects_seq'),
sect text NOT NULL,
PRIMARY KEY(sect_id)
);
/*
* Table depicting the names of the different sets that have been
* released so far
*/
CREATE TABLE sets
(
set_id int2 DEFAULT NEXTVAL('sets_seq'),
set_abbr varchar(6) NOT NULL,
set text NOT NULL,
PRIMARY KEY(set_id)
);
/*
* Table depicting the rarity of a card and/or if the card is
* included in a starter pack
*/
CREATE TABLE rarities
(
rarity_id int2 DEFAULT NEXTVAL('rarities_seq'),
rarity varchar(16) NOT NULL,
PRIMARY KEY(rarity_id)
);
/*
* Table depicting the names of the disciplines the vampires can have
*/
CREATE TABLE disciplines
(
discipline_id int2 DEFAULT NEXTVAL('disciplines_seq'),
discipline_abbr char(3) NOT NULL,
discipline text NOT NULL,
PRIMARY KEY(discipline_id)
);
/*
* Table depicting the artists who draw the different pictures on the
* cards
*/
CREATE TABLE artists
(
artist_id int2 DEFAULT NEXTVAL('artists_seq'),
artist text NOT NULL,
PRIMARY KEY(artist_id)
);
/*
* Table depicting which card types exists in V:TES
*/
CREATE TABLE cardtypes
(
cardtype_id int2 DEFAULT NEXTVAL('cardtypes_seq'),
cardtype text NOT NULL,
PRIMARY KEY(cardtype_id)
);
/*
* Table depicting the different titles a vampire can have
*/
CREATE TABLE titles
(
title_id int2 DEFAULT NEXTVAL('titles_seq'),
title text NOT NULL,
PRIMARY KEY(title_id)
);
/*
* Table depicting the names of the different clans
*/
CREATE TABLE clans
(
clan_id int2 DEFAULT NEXTVAL('clans_seq'),
clan text NOT NULL,
PRIMARY KEY(clan_id)
);
/*
* Table depicting the which cards exist and main information about
* them
*/
CREATE TABLE cards
(
card_id int2 DEFAULT NEXTVAL('cards_seq'),
cardname text NOT NULL,
PRIMARY KEY(card_id)
);
CREATE TABLE cards_costs
(
card_id int2 NOT NULL,
cost int2 NOT NULL,
pool bool NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards
);
/*
* Table depicting the alternative name of a card
*/
CREATE TABLE cards_names
(
card_id int2 NOT NULL,
cardname text NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards
);
/*
* Table depicting the rarity value of a card
*/
CREATE TABLE cards_rarities
(
card_id int2 NOT NULL,
rarity_id int2 NULL,
FOREIGN KEY (card_id) REFERENCES cards,
FOREIGN KEY (rarity_id) REFERENCES rarities
);
/*
* Table depicting which sets a card is included in
*/
CREATE TABLE cards_sets
(
card_id int2 NOT NULL,
set_id int2 NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards,
FOREIGN KEY (set_id) REFERENCES sets
);
/*
* Table depicting which artist/artists drew the picture on a card
*/
CREATE TABLE cards_artists
(
card_id int2 NOT NULL,
artist_id int2 NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards,
FOREIGN KEY (artist_id) REFERENCES artists
);
/*
* Table depicting which type/types of cards exist
*/
CREATE TABLE cards_cardtypes
(
card_id int2 NOT NULL,
cardtype_id int2 NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards,
FOREIGN KEY (cardtype_id) REFERENCES cardtypes
);
/*
* Table depicting the capacity that a vampire has
*/
CREATE TABLE cards_capacities
(
card_id int2 NOT NULL,
capacity int2 NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards
);
/*
* Table depicting which discipline a vampire has
*/
CREATE TABLE cards_disciplines
(
card_id int2 NOT NULL,
discipline_id int2 NOT NULL,
superior boolean NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards
);
/*
* Table depicting clan association for a vampire card
*/
CREATE TABLE cards_clans
(
card_id int2 NOT NULL,
clan_id int2 NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards,
FOREIGN KEY (clan_id) REFERENCES clans
);
/*
* Table depicting the title for a vampire card
*/
CREATE TABLE cards_titles
(
card_id int2 NOT NULL,
title_id int2 NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards,
FOREIGN KEY (title_id) REFERENCES titles
);
/*
* Table depicting the sect for a vampire card
*/
CREATE TABLE cards_sects
(
card_id int2 NOT NULL,
sect_id int2 NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards,
FOREIGN KEY (sect_id) REFERENCES sects
);
/*
* Table depicting the text on a card
*/
CREATE TABLE cards_cardtexts
(
card_id int2 NOT NULL,
cardtext text NOT NULL,
FOREIGN KEY (card_id) REFERENCES cards
);
/*
* Table depicting the logins and passwords for the users of the
* website
*/
CREATE TABLE logins
(
login_id int4 DEFAULT NEXTVAL('logins_seq'),
login text NOT NULL,
password text NOT NULL,
firstname text NOT NULL,
lastname text NOT NULL,
address text NULL,
"zip code" text NULL,
city text NULL,
state text NULL,
country text NULL,
email text NULL,
PRIMARY KEY(login_id)
);
/*
* Table depicting the different cards and amount of cards that a
* user owns
*/
CREATE TABLE logins_cards
(
login_id int4,
card_id int2,
amount int2,
FOREIGN KEY (login_id) REFERENCES logins,
FOREIGN KEY (card_id) REFERENCES cards
);
/*
* Table depicting meta information about decks and which users owns
* which decks
*/
CREATE TABLE decks
(
deck_id int4 DEFAULT NEXTVAL('decks_seq'),
login_id int4,
PRIMARY KEY(deck_id),
FOREIGN KEY (login_id) REFERENCES logins
);
/*
* Table depicting the different decks, the type and amount of cards
* that is included each deck
*/
CREATE TABLE decks_cards
(
deck_id int4,
card_id int2,
amount int2,
FOREIGN KEY (deck_id) REFERENCES decks,
FOREIGN KEY (card_id) REFERENCES cards
);
/*
* Table depicting if a user is intrested in trading cards, both letting
* go and aquiring
*/
CREATE TABLE tradelist
(
login_id int2,
card_id int2,
want bool,
amount int2,
FOREIGN KEY (login_id) REFERENCES logins,
FOREIGN KEY (card_id) REFERENCES cards
);
CREATE INDEX cards_artists_index ON cards_artists (card_id);
CREATE INDEX cards_capacities_index ON cards_capacities (card_id);
CREATE INDEX cards_cardtexts_index ON cards_cardtexts (card_id);
CREATE INDEX cards_cardtypes_index ON cards_cardtypes (card_id);
CREATE INDEX cards_clans_index ON cards_clans (card_id);
CREATE INDEX cards_costs_index ON cards_costs (card_id);
CREATE INDEX cards_disciplines_index ON cards_disciplines (card_id);
CREATE INDEX cards_names_index ON cards_names (card_id);
CREATE INDEX cards_rarities_index ON cards_rarities (card_id);
CREATE INDEX cards_sects_index ON cards_sects (card_id);
CREATE INDEX cards_sets_index ON cards_sets (card_id);
CREATE INDEX cards_titles_index ON cards_titles (card_id);
CREATE INDEX decks_cards_index ON decks_cards (deck_id);
CREATE INDEX logins_cards_index ON logins_cards (login_id);
CREATE INDEX tradelist_index ON tradelist (login_id);
CREATE INDEX decks_index ON decks (login_id);
CREATE INDEX cards_artists2_index ON cards_artists (artist_id);
CREATE INDEX cards_cardtypes2_index ON cards_cardtypes (cardtype_id);
CREATE INDEX cards_clans2_index ON cards_clans (clan_id);
CREATE INDEX cards_disciplines2_index ON cards_disciplines (discipline_id);
CREATE INDEX cards_rarities2_index ON cards_rarities (rarity_id);
CREATE INDEX cards_sects2_index ON cards_sects (sect_id);
CREATE INDEX cards_sets2_index ON cards_sets (set_id);
CREATE INDEX cards_titles2_index ON cards_titles (title_id);
CREATE INDEX decks_cards2_index ON decks_cards (card_id);
CREATE INDEX logins_cards2_index ON logins_cards (card_id);
CREATE INDEX tradelist2_index ON tradelist (card_id);
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance problem, what to do?
2001-12-18 12:34 Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
2001-12-18 12:58 ` Re: Performance problem, what to do? Markus Bertheau <twanger@bluetwanger.de>
2001-12-18 13:32 ` Re: Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
@ 2001-12-18 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-18 15:50 ` Re: Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2001-12-18 15:36 UTC (permalink / raw)
To: Archibald Zimonyi <archie@netg.se>; +Cc: pgsql-sql
Archibald Zimonyi <archie@netg.se> writes:
> On demand, I'll post the database structure as well as the SQL query which
> creates the slow respons. I'll add them as attachments (do they come at
> all?).
You're forcing the join order by using explicit JOIN syntax.
Rearranging the join order might yield a better plan. See
http://www.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Performance problem, what to do?
2001-12-18 12:34 Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
2001-12-18 12:58 ` Re: Performance problem, what to do? Markus Bertheau <twanger@bluetwanger.de>
2001-12-18 13:32 ` Re: Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
2001-12-18 15:36 ` Re: Performance problem, what to do? Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-12-18 15:50 ` Archibald Zimonyi <archie@netg.se>
0 siblings, 0 replies; 5+ messages in thread
From: Archibald Zimonyi @ 2001-12-18 15:50 UTC (permalink / raw)
To: pgsql-sql
> Archibald Zimonyi <archie@netg.se> writes:
> > On demand, I'll post the database structure as well as the SQL query which
> > creates the slow respons. I'll add them as attachments (do they come at
> > all?).
>
> You're forcing the join order by using explicit JOIN syntax.
> Rearranging the join order might yield a better plan. See
> http://www.postgresql.org/users-lounge/docs/7.1/postgres/explicit-joins.html
>
I know I am, but if you look at the plan (which I will again add as an
attachment) you see that the plan looks pretty good, in fact way better
then the original plan did.
I don't know how much time each "unit" takes, but a normal select (SELECT
* FROM <table>), which might yield a plan of 0..22.2 "units", takes no
time at all. This plan is at most up at 200, which I don't think is costly
considering what it does.
Archie
Attachments:
explain.txt (text/plain, 4.7K) download
NOTICE: QUERY PLAN:
Nested Loop (cost=94.60..199.12 rows=1 width=148)
InitPlan
-> Seq Scan on cardtypes (cost=0.00..1.14 rows=1 width=2)
-> Nested Loop (cost=93.17..160.81 rows=1 width=130)
-> Nested Loop (cost=93.17..151.95 rows=1 width=112)
-> Nested Loop (cost=73.59..131.84 rows=1 width=98)
-> Nested Loop (cost=72.49..123.45 rows=1 width=84)
-> Nested Loop (cost=15.67..66.45 rows=1 width=70)
-> Nested Loop (cost=15.67..64.42 rows=1 width=66)
-> Nested Loop (cost=15.67..62.39 rows=1 width=62)
-> Nested Loop (cost=15.67..60.36 rows=1 width=48)
-> Nested Loop (cost=15.67..58.95 rows=1 width=34)
-> Hash Join (cost=15.67..55.83 rows=2 width=20)
-> Nested Loop (cost=0.00..34.54 rows=127 width=18)
-> Seq Scan on cardtypes c (cost=0.00..1.11 rows=11 width=14)
-> Index Scan using cards_cardtypes2_index on cards_cardtypes ct (cost=0.00..3.02 rows=1 width=4)
-> Hash (cost=15.57..15.57 rows=42 width=2)
-> Index Scan using cards_cardtypes2_index on cards_cardtypes (cost=0.00..15.57 rows=42 width=2)
-> Index Scan using cards_pkey on cards c (cost=0.00..2.01 rows=1 width=14)
-> Seq Scan on cards_names (cost=0.00..1.18 rows=18 width=14)
-> Index Scan using cards_cardtexts_index on cards_cardtexts (cost=0.00..2.01 rows=1 width=14)
-> Index Scan using cards_capacities_index on cards_capacities (cost=0.00..2.01 rows=1 width=4)
-> Index Scan using cards_costs_index on cards_costs (cost=0.00..2.01 rows=1 width=4)
-> Subquery Scan t_disciplines (cost=56.82..56.96 rows=3 width=19)
-> Aggregate (cost=56.82..56.96 rows=3 width=19)
-> Group (cost=56.82..56.89 rows=29 width=19)
-> Sort (cost=56.82..56.82 rows=29 width=19)
-> Nested Loop (cost=0.00..56.11 rows=29 width=19)
-> Seq Scan on disciplines d (cost=0.00..1.27 rows=27 width=14)
-> Index Scan using cards_disciplines2_index on cards_disciplines cd (cost=0.00..2.02 rows=1 width=5)
-> Subquery Scan t_titles (cost=1.10..8.26 rows=10 width=18)
-> Hash Join (cost=1.10..8.26 rows=10 width=18)
-> Seq Scan on cards_titles ct (cost=0.00..2.15 rows=115 width=4)
-> Hash (cost=1.08..1.08 rows=8 width=14)
-> Seq Scan on titles t (cost=0.00..1.08 rows=8 width=14)
-> Subquery Scan t_sets (cost=19.58..19.94 rows=7 width=18)
-> Aggregate (cost=19.58..19.94 rows=7 width=18)
-> Group (cost=19.58..19.76 rows=73 width=18)
-> Sort (cost=19.58..19.58 rows=73 width=18)
-> Nested Loop (cost=0.00..17.33 rows=73 width=18)
-> Seq Scan on sets s (cost=0.00..1.08 rows=8 width=14)
-> Index Scan using cards_sets2_index on cards_sets cs (cost=0.00..2.02 rows=1 width=4)
-> Materialize (cost=7.12..7.12 rows=70 width=18)
-> Nested Loop (cost=0.00..7.12 rows=70 width=18)
-> Seq Scan on sects s (cost=0.00..1.03 rows=3 width=14)
-> Index Scan using cards_sects2_index on cards_sects cs (cost=0.00..2.02 rows=1 width=4)
-> Materialize (cost=38.17..38.17 rows=5 width=18)
-> Hash Join (cost=1.43..38.17 rows=5 width=18)
-> Seq Scan on cards_clans cc (cost=0.00..10.14 rows=614 width=4)
-> Hash (cost=1.34..1.34 rows=34 width=14)
-> Seq Scan on clans c (cost=0.00..1.34 rows=34 width=14)
EXPLAIN
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2001-12-18 15:50 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-18 12:34 Performance problem, what to do? Archibald Zimonyi <archie@netg.se>
2001-12-18 12:58 ` Markus Bertheau <twanger@bluetwanger.de>
2001-12-18 13:32 ` Archibald Zimonyi <archie@netg.se>
2001-12-18 15:36 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-12-18 15:50 ` Archibald Zimonyi <archie@netg.se>
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