Thread: Join between 2 tables always executes a sequential scan on the larger table
Join between 2 tables always executes a sequential scan on the larger table
From
Dieter Rehbein
Date:
Hi everybody,
in a project I have a performance problem, which I (and my colleagues) don't understand. It's a simple join between 2 of 3 tables:
table-1: user (id, user_name, ...). This table has about 1 million rows (999673 rows)
table-2: competition (57 rows)
table-3: user_2_competition. A relation between user and competition. This table has about 100.000 rows
The query is a join between table user_2_competition and user and looks like this:
select u.id, u.user_name
from user_2_competition uc
left join "user" u on u.id = uc.user_id
where uc.competition_id = '3cc1cb9b3ac132ad013ad01316040001'
The query returns the ID and user_name of all users participating in a competition.
What I don't understand: This query executes a sequential scan on user!
The tables have the following indexes:
user_2_competition: there is an index on user_id and an index on competition_id (competition_id is a VARCHAR(32) containing UUIDs)
user: id is the primary key and has therefore a unique index (the ID is a VARCHAR(32), which contains UUIDs).
The database has just been restored from a backup, I've executed ANALYZE for both tables.
The output of explain analyze (Postgres 9.2.3):
Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
Hash Cond: ((uc.user_id)::text = (u.id)::text)
-> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691 rows=41333 loops=1)
Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
Rows Removed by Filter: 80684
-> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
Buckets: 2048 Batches: 128 Memory Usage: 589kB
-> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673 loops=1)
Total runtime: 2740.723 ms
I expected to see an index-scan on user_2_competition with a hash join to user, not a sequential scan on user. I've tried this with Postgres 9.1 and 9.2.3).
Any ideas, what's going on here?
With EXPLAIN ANALYZE I can see, which query plan Postgres is using. Is there any way to find out, WHY postgres uses this query plan?
best regards
Dieter
----------------------------------------------------
The full table schema:
CREATE TABLE user_2_competition
(
id varchar(32) NOT NULL,
version int4 NOT NULL DEFAULT 0,
conditions_confirm_ip varchar(30),
created_date timestamp NOT NULL DEFAULT now(),
deleted bool NOT NULL DEFAULT false,
last_visit timestamp,
resort_id int4,
role varchar(255),
caid int4 NOT NULL,
ponr int4 NOT NULL,
ktka int4 NOT NULL,
lfnr int4 NOT NULL,
total_visits int8 NOT NULL DEFAULT 0,
verified bool NOT NULL,
competition_id varchar(32),
user_id varchar(32),
competition_terms int4 NOT NULL DEFAULT (-1),
disqualified bool NOT NULL DEFAULT false,
registration_key_id int4,
PRIMARY KEY(id)
);
-- Indexes ------------------------------------------------------------
CREATE INDEX IDX_USER_ID ON user_2_competition USING btree (user_id);
CREATE INDEX idx_user_2_competition_competition ON user_2_competition USING btree (competition_id);
CREATE UNIQUE INDEX user_2_competition_user_id_competition_id_key ON user_2_competition USING btree (user_id, competition_id);
-- Foreign key constraints -------------------------------------------
ALTER TABLE user_2_competition
ADD CONSTRAINT fk_user_competition_competition_group
FOREIGN KEY (competition_id) REFERENCES competition (id) ON DELETE CASCADE;
ALTER TABLE user_2_competition
ADD CONSTRAINT fk_user_2_competition_registration_key
FOREIGN KEY (registration_key_id) REFERENCES competition_registration_key (id);
ALTER TABLE user_2_competition
ADD CONSTRAINT fk_user_competition_terms
FOREIGN KEY (competition_terms) REFERENCES competition_terms (id);
ALTER TABLE user_2_competition
ADD CONSTRAINT fk_user_competition_user
FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;
-----------------
CREATE TABLE competition
(
id varchar(32) NOT NULL,
version int4 NOT NULL DEFAULT 0,
created_by varchar(255),
created_date timestamp,
modified_by varchar(255),
modified_date timestamp,
deleted bool NOT NULL DEFAULT false,
active bool NOT NULL DEFAULT false,
average_score float8,
start_time timestamp NOT NULL,
end_time timestamp NOT NULL,
info_layout varchar(200),
list_layout varchar(200),
lead_action varchar(100),
ranking_layout varchar(200),
external_url varchar(255),
forum_enabled bool NOT NULL DEFAULT false,
has_ski_movies bool NOT NULL DEFAULT false,
link_name varchar(50) NOT NULL,
participation_type varchar(255) NOT NULL,
sponsor varchar(100),
custom_style bool NOT NULL DEFAULT true,
bg_color varchar(7),
tab_style varchar(20),
background_image_preview_upload_date timestamp,
background_image_upload_date timestamp,
sponsor_logo_upload_date timestamp,
name int4 NOT NULL,
short_name int4 NOT NULL,
description int4 NOT NULL,
teaser int4 NOT NULL,
tags varchar(1000),
logo_resort_id int4,
visible bool NOT NULL DEFAULT true,
time_zone_id varchar(32) NOT NULL DEFAULT 'Europe/Vienna'::character varying,
css_styles varchar(2000),
teaser_popup int4 NOT NULL DEFAULT (-1),
winner_tab int4 NOT NULL DEFAULT (-1),
reminder_email int4 NOT NULL DEFAULT (-1),
reminder_email_subject int4 NOT NULL DEFAULT (-1),
priority int4 NOT NULL DEFAULT 5,
instance_selector_class_name varchar(200),
external_sponsor_logo_upload_date timestamp,
customer_id varchar(10),
restricted_registration bool NOT NULL DEFAULT false,
PRIMARY KEY(id)
);
-- Indexes ------------------------------------------------------------
CREATE UNIQUE INDEX idx_competition_link_name ON competition USING btree (link_name);
-- Foreign key constraints -------------------------------------------
ALTER TABLE competition
ADD CONSTRAINT fk_competition_description
FOREIGN KEY (description) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_name
FOREIGN KEY (name) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_reminder_email
FOREIGN KEY (reminder_email) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_reminder_subject
FOREIGN KEY (reminder_email_subject) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_short_name
FOREIGN KEY (short_name) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_teaser
FOREIGN KEY (teaser) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_teaser_popup
FOREIGN KEY (teaser_popup) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_winner_tab
FOREIGN KEY (winner_tab) REFERENCES localized_text (id);
CREATE TABLE user
(
id varchar(32) NOT NULL,
version int4 NOT NULL DEFAULT 0,
deleted bool NOT NULL DEFAULT false,
about_me varchar(8000),
birth_date date,
communicated_to_ticket_corner timestamp,
conditions_confirm_date timestamp,
email varchar(125) NOT NULL,
fname varchar(50) NOT NULL,
gender varchar(10),
lname varchar(50) NOT NULL,
old_skiline_id int4,
photo_upload_date timestamp,
news_letter bool NOT NULL DEFAULT true,
newsfeed_notification varchar(20),
preferred_language varchar(16),
privacy_address varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_basic_data varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_community_accounts varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_email varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_fitness_profile varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_phone_numbers varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_race_profile varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_rankings_user_name varchar(10) DEFAULT 'NO_DISPLAY'::character varying,
search_email varchar(125),
search_name varchar(110),
status_points int4 NOT NULL DEFAULT 0,
ticket_corner_id int4,
user_name varchar(50) NOT NULL,
user_name_deleted varchar(50),
address varchar(32) NOT NULL,
current_fitness_profile varchar(32),
race_profile varchar(32) NOT NULL,
custom1 varchar(255),
custom2 varchar(255),
custom3 varchar(255),
magento_customer_id int4,
created_by varchar(255),
created_date timestamp,
modified_by varchar(255),
modified_date timestamp,
newsfeed varchar(32),
birth_day int4,
estimated_gender varchar(10),
current_season_statistics int4 NOT NULL DEFAULT (-1),
statistic_competition_count int4 NOT NULL DEFAULT 0,
statistic_friend_count int4 NOT NULL DEFAULT 0,
statistic_group_count int4 NOT NULL DEFAULT 0,
statistic_skimovie_count_friends int4 NOT NULL DEFAULT 0,
statistic_skimovie_count_public int4 NOT NULL DEFAULT 0,
statistic_skimovie_count_all int4 NOT NULL DEFAULT 0,
statistic_photo_count_public int4 NOT NULL DEFAULT 0,
statistic_photo_count_friends int4 NOT NULL DEFAULT 0,
statistic_photo_count_all int4 NOT NULL DEFAULT 0,
privacy_calendar varchar(10) DEFAULT 'FRIENDS'::character varying,
security_info_id varchar(32),
statistic_skiing_days int4 NOT NULL DEFAULT 0,
statistic_vertical_meters int4 NOT NULL DEFAULT 0,
conditions_confirm_ip varchar(30),
doi_click_ip varchar(30),
staff bool,
origin varchar(32),
disqualified bool,
statistic_badge_count int4 NOT NULL DEFAULT 0,
time_zone_id varchar(32),
old_email varchar(125),
handicap float4,
previous_handicap float4,
handicap_calculation_time timestamp,
last_skiing_day date,
admin_disqualification bool,
admin_disqualification_top100 bool,
PRIMARY KEY(id)
);
-- Indexes ------------------------------------------------------------
CREATE INDEX idx_user_birthdate ON user USING btree (birth_day);
CREATE INDEX idx_user_created_date ON user USING btree (created_date);
CREATE INDEX idx_user_email ON user USING btree (email);
CREATE INDEX idx_user_magento_customer_id ON user USING btree (magento_customer_id);
CREATE INDEX idx_usr_modified_date ON user USING btree (modified_date);
CREATE UNIQUE INDEX user_address_key ON user USING btree (address);
CREATE UNIQUE INDEX user_race_profile_key ON user USING btree (race_profile);
CREATE UNIQUE INDEX user_ticket_corner_id_key ON user USING btree (ticket_corner_id);
CREATE UNIQUE INDEX user_user_name_key ON user USING btree (user_name);
-- Foreign key constraints -------------------------------------------
ALTER TABLE user
ADD CONSTRAINT fk_user_adress
FOREIGN KEY (address) REFERENCES address (id) ON DELETE CASCADE;
ALTER TABLE user
ADD CONSTRAINT fk36ebcbd93f2254
FOREIGN KEY (current_fitness_profile) REFERENCES fitness_profile (id);
ALTER TABLE user
ADD CONSTRAINT fk_user_newsfeed
FOREIGN KEY (newsfeed) REFERENCES newsfeed (id);
ALTER TABLE user
ADD CONSTRAINT fk36ebcbd70f10c
FOREIGN KEY (race_profile) REFERENCES race_profile (id);
ALTER TABLE user
ADD CONSTRAINT fk_user_sec_info
FOREIGN KEY (security_info_id) REFERENCES security_info (id) ON DELETE CASCADE;
ALTER TABLE user
ADD CONSTRAINT fk_user_statistics_current_season
FOREIGN KEY (current_season_statistics) REFERENCES user_season_statistics (id);
in a project I have a performance problem, which I (and my colleagues) don't understand. It's a simple join between 2 of 3 tables:
table-1: user (id, user_name, ...). This table has about 1 million rows (999673 rows)
table-2: competition (57 rows)
table-3: user_2_competition. A relation between user and competition. This table has about 100.000 rows
The query is a join between table user_2_competition and user and looks like this:
select u.id, u.user_name
from user_2_competition uc
left join "user" u on u.id = uc.user_id
where uc.competition_id = '3cc1cb9b3ac132ad013ad01316040001'
The query returns the ID and user_name of all users participating in a competition.
What I don't understand: This query executes a sequential scan on user!
The tables have the following indexes:
user_2_competition: there is an index on user_id and an index on competition_id (competition_id is a VARCHAR(32) containing UUIDs)
user: id is the primary key and has therefore a unique index (the ID is a VARCHAR(32), which contains UUIDs).
The database has just been restored from a backup, I've executed ANALYZE for both tables.
The output of explain analyze (Postgres 9.2.3):
Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
Hash Cond: ((uc.user_id)::text = (u.id)::text)
-> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691 rows=41333 loops=1)
Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
Rows Removed by Filter: 80684
-> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
Buckets: 2048 Batches: 128 Memory Usage: 589kB
-> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673 loops=1)
Total runtime: 2740.723 ms
I expected to see an index-scan on user_2_competition with a hash join to user, not a sequential scan on user. I've tried this with Postgres 9.1 and 9.2.3).
Any ideas, what's going on here?
With EXPLAIN ANALYZE I can see, which query plan Postgres is using. Is there any way to find out, WHY postgres uses this query plan?
best regards
Dieter
----------------------------------------------------
The full table schema:
CREATE TABLE user_2_competition
(
id varchar(32) NOT NULL,
version int4 NOT NULL DEFAULT 0,
conditions_confirm_ip varchar(30),
created_date timestamp NOT NULL DEFAULT now(),
deleted bool NOT NULL DEFAULT false,
last_visit timestamp,
resort_id int4,
role varchar(255),
caid int4 NOT NULL,
ponr int4 NOT NULL,
ktka int4 NOT NULL,
lfnr int4 NOT NULL,
total_visits int8 NOT NULL DEFAULT 0,
verified bool NOT NULL,
competition_id varchar(32),
user_id varchar(32),
competition_terms int4 NOT NULL DEFAULT (-1),
disqualified bool NOT NULL DEFAULT false,
registration_key_id int4,
PRIMARY KEY(id)
);
-- Indexes ------------------------------------------------------------
CREATE INDEX IDX_USER_ID ON user_2_competition USING btree (user_id);
CREATE INDEX idx_user_2_competition_competition ON user_2_competition USING btree (competition_id);
CREATE UNIQUE INDEX user_2_competition_user_id_competition_id_key ON user_2_competition USING btree (user_id, competition_id);
-- Foreign key constraints -------------------------------------------
ALTER TABLE user_2_competition
ADD CONSTRAINT fk_user_competition_competition_group
FOREIGN KEY (competition_id) REFERENCES competition (id) ON DELETE CASCADE;
ALTER TABLE user_2_competition
ADD CONSTRAINT fk_user_2_competition_registration_key
FOREIGN KEY (registration_key_id) REFERENCES competition_registration_key (id);
ALTER TABLE user_2_competition
ADD CONSTRAINT fk_user_competition_terms
FOREIGN KEY (competition_terms) REFERENCES competition_terms (id);
ALTER TABLE user_2_competition
ADD CONSTRAINT fk_user_competition_user
FOREIGN KEY (user_id) REFERENCES user (id) ON DELETE CASCADE;
-----------------
CREATE TABLE competition
(
id varchar(32) NOT NULL,
version int4 NOT NULL DEFAULT 0,
created_by varchar(255),
created_date timestamp,
modified_by varchar(255),
modified_date timestamp,
deleted bool NOT NULL DEFAULT false,
active bool NOT NULL DEFAULT false,
average_score float8,
start_time timestamp NOT NULL,
end_time timestamp NOT NULL,
info_layout varchar(200),
list_layout varchar(200),
lead_action varchar(100),
ranking_layout varchar(200),
external_url varchar(255),
forum_enabled bool NOT NULL DEFAULT false,
has_ski_movies bool NOT NULL DEFAULT false,
link_name varchar(50) NOT NULL,
participation_type varchar(255) NOT NULL,
sponsor varchar(100),
custom_style bool NOT NULL DEFAULT true,
bg_color varchar(7),
tab_style varchar(20),
background_image_preview_upload_date timestamp,
background_image_upload_date timestamp,
sponsor_logo_upload_date timestamp,
name int4 NOT NULL,
short_name int4 NOT NULL,
description int4 NOT NULL,
teaser int4 NOT NULL,
tags varchar(1000),
logo_resort_id int4,
visible bool NOT NULL DEFAULT true,
time_zone_id varchar(32) NOT NULL DEFAULT 'Europe/Vienna'::character varying,
css_styles varchar(2000),
teaser_popup int4 NOT NULL DEFAULT (-1),
winner_tab int4 NOT NULL DEFAULT (-1),
reminder_email int4 NOT NULL DEFAULT (-1),
reminder_email_subject int4 NOT NULL DEFAULT (-1),
priority int4 NOT NULL DEFAULT 5,
instance_selector_class_name varchar(200),
external_sponsor_logo_upload_date timestamp,
customer_id varchar(10),
restricted_registration bool NOT NULL DEFAULT false,
PRIMARY KEY(id)
);
-- Indexes ------------------------------------------------------------
CREATE UNIQUE INDEX idx_competition_link_name ON competition USING btree (link_name);
-- Foreign key constraints -------------------------------------------
ALTER TABLE competition
ADD CONSTRAINT fk_competition_description
FOREIGN KEY (description) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_name
FOREIGN KEY (name) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_reminder_email
FOREIGN KEY (reminder_email) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_reminder_subject
FOREIGN KEY (reminder_email_subject) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_short_name
FOREIGN KEY (short_name) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_teaser
FOREIGN KEY (teaser) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_teaser_popup
FOREIGN KEY (teaser_popup) REFERENCES localized_text (id);
ALTER TABLE competition
ADD CONSTRAINT fk_competition_winner_tab
FOREIGN KEY (winner_tab) REFERENCES localized_text (id);
CREATE TABLE user
(
id varchar(32) NOT NULL,
version int4 NOT NULL DEFAULT 0,
deleted bool NOT NULL DEFAULT false,
about_me varchar(8000),
birth_date date,
communicated_to_ticket_corner timestamp,
conditions_confirm_date timestamp,
email varchar(125) NOT NULL,
fname varchar(50) NOT NULL,
gender varchar(10),
lname varchar(50) NOT NULL,
old_skiline_id int4,
photo_upload_date timestamp,
news_letter bool NOT NULL DEFAULT true,
newsfeed_notification varchar(20),
preferred_language varchar(16),
privacy_address varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_basic_data varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_community_accounts varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_email varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_fitness_profile varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_phone_numbers varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_race_profile varchar(10) NOT NULL DEFAULT 'FRIENDS'::character varying,
privacy_rankings_user_name varchar(10) DEFAULT 'NO_DISPLAY'::character varying,
search_email varchar(125),
search_name varchar(110),
status_points int4 NOT NULL DEFAULT 0,
ticket_corner_id int4,
user_name varchar(50) NOT NULL,
user_name_deleted varchar(50),
address varchar(32) NOT NULL,
current_fitness_profile varchar(32),
race_profile varchar(32) NOT NULL,
custom1 varchar(255),
custom2 varchar(255),
custom3 varchar(255),
magento_customer_id int4,
created_by varchar(255),
created_date timestamp,
modified_by varchar(255),
modified_date timestamp,
newsfeed varchar(32),
birth_day int4,
estimated_gender varchar(10),
current_season_statistics int4 NOT NULL DEFAULT (-1),
statistic_competition_count int4 NOT NULL DEFAULT 0,
statistic_friend_count int4 NOT NULL DEFAULT 0,
statistic_group_count int4 NOT NULL DEFAULT 0,
statistic_skimovie_count_friends int4 NOT NULL DEFAULT 0,
statistic_skimovie_count_public int4 NOT NULL DEFAULT 0,
statistic_skimovie_count_all int4 NOT NULL DEFAULT 0,
statistic_photo_count_public int4 NOT NULL DEFAULT 0,
statistic_photo_count_friends int4 NOT NULL DEFAULT 0,
statistic_photo_count_all int4 NOT NULL DEFAULT 0,
privacy_calendar varchar(10) DEFAULT 'FRIENDS'::character varying,
security_info_id varchar(32),
statistic_skiing_days int4 NOT NULL DEFAULT 0,
statistic_vertical_meters int4 NOT NULL DEFAULT 0,
conditions_confirm_ip varchar(30),
doi_click_ip varchar(30),
staff bool,
origin varchar(32),
disqualified bool,
statistic_badge_count int4 NOT NULL DEFAULT 0,
time_zone_id varchar(32),
old_email varchar(125),
handicap float4,
previous_handicap float4,
handicap_calculation_time timestamp,
last_skiing_day date,
admin_disqualification bool,
admin_disqualification_top100 bool,
PRIMARY KEY(id)
);
-- Indexes ------------------------------------------------------------
CREATE INDEX idx_user_birthdate ON user USING btree (birth_day);
CREATE INDEX idx_user_created_date ON user USING btree (created_date);
CREATE INDEX idx_user_email ON user USING btree (email);
CREATE INDEX idx_user_magento_customer_id ON user USING btree (magento_customer_id);
CREATE INDEX idx_usr_modified_date ON user USING btree (modified_date);
CREATE UNIQUE INDEX user_address_key ON user USING btree (address);
CREATE UNIQUE INDEX user_race_profile_key ON user USING btree (race_profile);
CREATE UNIQUE INDEX user_ticket_corner_id_key ON user USING btree (ticket_corner_id);
CREATE UNIQUE INDEX user_user_name_key ON user USING btree (user_name);
-- Foreign key constraints -------------------------------------------
ALTER TABLE user
ADD CONSTRAINT fk_user_adress
FOREIGN KEY (address) REFERENCES address (id) ON DELETE CASCADE;
ALTER TABLE user
ADD CONSTRAINT fk36ebcbd93f2254
FOREIGN KEY (current_fitness_profile) REFERENCES fitness_profile (id);
ALTER TABLE user
ADD CONSTRAINT fk_user_newsfeed
FOREIGN KEY (newsfeed) REFERENCES newsfeed (id);
ALTER TABLE user
ADD CONSTRAINT fk36ebcbd70f10c
FOREIGN KEY (race_profile) REFERENCES race_profile (id);
ALTER TABLE user
ADD CONSTRAINT fk_user_sec_info
FOREIGN KEY (security_info_id) REFERENCES security_info (id) ON DELETE CASCADE;
ALTER TABLE user
ADD CONSTRAINT fk_user_statistics_current_season
FOREIGN KEY (current_season_statistics) REFERENCES user_season_statistics (id);
Re: Join between 2 tables always executes a sequential scan on the larger table
From
Igor Neyman
Date:
From: Dieter Rehbein [mailto:dieter.rehbein@skiline.cc] Sent: Tuesday, April 02, 2013 4:52 AM To: pgsql-performance@postgresql.org Subject: Join between 2 tables always executes a sequential scan on the larger table Hi everybody, in a project I have a performance problem, which I (and my colleagues) don't understand. It's a simple join between 2 of3 tables: table-1: user (id, user_name, ...). This table has about 1 million rows (999673 rows) table-2: competition (57 rows) table-3: user_2_competition. A relation between user and competition. This table has about 100.000 rows The query is a join between table user_2_competition and user and looks like this: select u.id, u.user_name from user_2_competition uc left join "user" u on u.id = uc.user_id where uc.competition_id = '3cc1cb9b3ac132ad013ad01316040001' The query returns the ID and user_name of all users participating in a competition. What I don't understand: This query executes a sequential scan on user! The tables have the following indexes: user_2_competition: there is an index on user_id and an index on competition_id (competition_id is a VARCHAR(32) containingUUIDs) user: id is the primary key and has therefore a unique index (the ID is a VARCHAR(32), which contains UUIDs). The database has just been restored from a backup, I've executed ANALYZE for both tables. The output of explain analyze (Postgres 9.2.3): Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1) Hash Cond: ((uc.user_id)::text = (u.id)::text) -> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691 rows=41333 loops=1) Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text) Rows Removed by Filter: 80684 -> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1) Buckets: 2048 Batches: 128 Memory Usage: 589kB -> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673 loops=1) Total runtime: 2740.723 ms I expected to see an index-scan on user_2_competition with a hash join to user, not a sequential scan on user. I've triedthis with Postgres 9.1 and 9.2.3). Any ideas, what's going on here? With EXPLAIN ANALYZE I can see, which query plan Postgres is using. Is there any way to find out, WHY postgres uses thisquery plan? best regards Dieter ----------------------------------------------- Dieter, why do you think index-scan on user_2_competition would be better? Based on huge number of rows returned (41333 out of total ~120000 in the table) from this table optimizer decided that SeqScan is better than index scan. You don't show QUERY TUNING parameters from Postgresql.conf, are they default? Playing with optimizer parameters (lowering random_page_cost, lowering cpu_index_tuple_cost , increasing effective_cache_size,or just setting enable_seqscan = off), you could try to force "optimizer" to use index, and see if youare getting better results. Regards, Igor Neyman
Re: Re: Join between 2 tables always executes a sequential scan on the larger table
From
Tom Lane
Date:
Igor Neyman <ineyman@perceptron.com> writes:
> The output of explain analyze (Postgres 9.2.3):
> Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
> Hash Cond: ((uc.user_id)::text = (u.id)::text)
> -> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691
rows=41333loops=1)
> Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
> Rows Removed by Filter: 80684
> -> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
> Buckets: 2048 Batches: 128 Memory Usage: 589kB
> -> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673
loops=1)
> Total runtime: 2740.723 ms
> I expected to see an index-scan on user_2_competition with a hash join to user, not a sequential scan on user. I've
triedthis with Postgres 9.1 and 9.2.3).
According to the numbers, you're fetching about a third of the
user_2_competition table, which is well past the point where an
indexscan is of any use. It's picking the seqscan because it thinks
that's faster, and I'm sure it's right.
The aspect of this plan that actually seems a bit dubious is that it's
hashing the larger input table rather than the smaller one. There's
a thread going on about that in -hackers right now; we think it's
probably putting too much emphasis on the distribution of the join key
as opposed to the size of the table.
One thing that would help is increasing work_mem --- it looks like you
are using the default 1MB. Cranking that up to a few MB would reduce
the number of hash batches needed.
regards, tom lane
Re: Join between 2 tables always executes a sequential scan on the larger table
From
Dieter Rehbein
Date:
Hi Igor,
thanks for the reply. The sequential scan on user_2_competition wasn't my main-problem. What really suprised me was
thesequential scan on table user, which is a sequential scan over one million rows.
Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
Hash Cond: ((uc.user_id)::text = (u.id)::text)
-> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691 rows=41333
loops=1)
Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
Rows Removed by Filter: 80684
-> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
Buckets: 2048 Batches: 128 Memory Usage: 589kB
-> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673
loops=1) <-- This sequential scan is strange.
IMHO the reason for the sequential scan on user is, that it is faster than an index-scan for 41333 rows. I've tried
thesame query using a different competition id with much less participants (about 1700). That query has a query plan as
expected:
Nested Loop Left Join (cost=0.00..21385.59 rows=1684 width=42) (actual time=1.317..147.781 rows=1757 loops=1)
-> Seq Scan on user_2_competition uc (cost=0.00..7026.93 rows=1684 width=33) (actual time=1.262..92.339 rows=1757
loops=1)
Filter: ((competition_id)::text = '3cc1cb963b988f12013bc737b4590001'::text)
-> Index Scan using user_pkey on "user" u (cost=0.00..8.51 rows=1 width=42) (actual time=0.030..0.031 rows=1
loops=1757)
Index Cond: ((id)::text = (uc.user_id)::text)
Total runtime: 148.068 ms
regards
Dieter
Am 02.04.2013 um 16:55 schrieb Igor Neyman <ineyman@perceptron.com>:
From: Dieter Rehbein [mailto:dieter.rehbein@skiline.cc]
Sent: Tuesday, April 02, 2013 4:52 AM
To: pgsql-performance@postgresql.org
Subject: Join between 2 tables always executes a sequential scan on the larger table
Hi everybody,
in a project I have a performance problem, which I (and my colleagues) don't understand. It's a simple join between 2
of3 tables:
table-1: user (id, user_name, ...). This table has about 1 million rows (999673 rows)
table-2: competition (57 rows)
table-3: user_2_competition. A relation between user and competition. This table has about 100.000 rows
The query is a join between table user_2_competition and user and looks like this:
select u.id, u.user_name
from user_2_competition uc
left join "user" u on u.id = uc.user_id
where uc.competition_id = '3cc1cb9b3ac132ad013ad01316040001'
The query returns the ID and user_name of all users participating in a competition.
What I don't understand: This query executes a sequential scan on user!
The tables have the following indexes:
user_2_competition: there is an index on user_id and an index on competition_id (competition_id is a VARCHAR(32)
containingUUIDs)
user: id is the primary key and has therefore a unique index (the ID is a VARCHAR(32), which contains UUIDs).
The database has just been restored from a backup, I've executed ANALYZE for both tables.
The output of explain analyze (Postgres 9.2.3):
Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
Hash Cond: ((uc.user_id)::text = (u.id)::text)
-> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691 rows=41333
loops=1)
Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
Rows Removed by Filter: 80684
-> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
Buckets: 2048 Batches: 128 Memory Usage: 589kB
-> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673
loops=1)
Total runtime: 2740.723 ms
I expected to see an index-scan on user_2_competition with a hash join to user, not a sequential scan on user. I've
triedthis with Postgres 9.1 and 9.2.3).
Any ideas, what's going on here?
With EXPLAIN ANALYZE I can see, which query plan Postgres is using. Is there any way to find out, WHY postgres uses
thisquery plan?
best regards
Dieter
-----------------------------------------------
Dieter,
why do you think index-scan on user_2_competition would be better?
Based on huge number of rows returned (41333 out of total ~120000 in the table) from this table optimizer decided that
SeqScan is better than index scan.
You don't show QUERY TUNING parameters from Postgresql.conf, are they default?
Playing with optimizer parameters (lowering random_page_cost, lowering cpu_index_tuple_cost , increasing
effective_cache_size,or just setting enable_seqscan = off), you could try to force "optimizer" to use index, and see if
youare getting better results.
Regards,
Igor Neyman
Happy Skiing!
Dieter Rehbein
Software Architect | dieter.rehbein@skiline.cc
Skiline Media GmbH
Lakeside B03
9020 Klagenfurt, Austria
fon: +43 463 249445-800
fax: +43 463 249445-102
"Erlebe Skifahren neu!"
CONFIDENTIALITY: This e-mail and any attachments are confidential and may also be privileged. If you are not the
designatedrecipient, please notify the sender immediately by reply e-mail and destroy all copies (digital and paper).
Anyunauthorized disclosure, distribution, copying, storage or use of the information contained in this e-mail or any
attachmentsis strictly prohibited and may be unlawful.
LEGAL: Skiline Media GmbH - Managing Director: Michael Saringer
Re: Join between 2 tables always executes a sequential scan on the larger table
From
Dieter Rehbein
Date:
HiTom,
thanks for your reply. It was the sequential scan on table user (about 1 million rows), which really surprised me. But
asequential scan over 1 million users seems to be more efficient than an index-Scan for 41.000 rows.
If a execute the query with the ID of a competiton with less participants, the query has a plan as expected:
Nested Loop Left Join (cost=0.00..21385.72 rows=1684 width=42) (actual time=1.300..138.223 rows=1757 loops=1)
-> Seq Scan on user_2_competition uc (cost=0.00..7026.93 rows=1684 width=33) (actual time=1.253..90.846 rows=1757
loops=1)
Filter: ((competition_id)::text = '3cc1cb963b988f12013bc737b4590001'::text)
-> Index Scan using user_pkey on "user" u (cost=0.00..8.51 rows=1 width=42) (actual time=0.026..0.026 rows=1
loops=1757)
Index Cond: ((id)::text = (uc.user_id)::text)
Total runtime: 138.505 ms
regards
Dieter
Am 02.04.2013 um 17:45 schrieb Tom Lane <tgl@sss.pgh.pa.us>:
Igor Neyman <ineyman@perceptron.com> writes:
> The output of explain analyze (Postgres 9.2.3):
> Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
> Hash Cond: ((uc.user_id)::text = (u.id)::text)
> -> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691
rows=41333loops=1)
> Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
> Rows Removed by Filter: 80684
> -> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
> Buckets: 2048 Batches: 128 Memory Usage: 589kB
> -> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673
loops=1)
> Total runtime: 2740.723 ms
> I expected to see an index-scan on user_2_competition with a hash join to user, not a sequential scan on user. I've
triedthis with Postgres 9.1 and 9.2.3).
According to the numbers, you're fetching about a third of the
user_2_competition table, which is well past the point where an
indexscan is of any use. It's picking the seqscan because it thinks
that's faster, and I'm sure it's right.
The aspect of this plan that actually seems a bit dubious is that it's
hashing the larger input table rather than the smaller one. There's
a thread going on about that in -hackers right now; we think it's
probably putting too much emphasis on the distribution of the join key
as opposed to the size of the table.
One thing that would help is increasing work_mem --- it looks like you
are using the default 1MB. Cranking that up to a few MB would reduce
the number of hash batches needed.
regards, tom lane
Happy Skiing!
Dieter Rehbein
Software Architect | dieter.rehbein@skiline.cc
Skiline Media GmbH
Lakeside B03
9020 Klagenfurt, Austria
fon: +43 463 249445-800
fax: +43 463 249445-102
"Erlebe Skifahren neu!"
CONFIDENTIALITY: This e-mail and any attachments are confidential and may also be privileged. If you are not the
designatedrecipient, please notify the sender immediately by reply e-mail and destroy all copies (digital and paper).
Anyunauthorized disclosure, distribution, copying, storage or use of the information contained in this e-mail or any
attachmentsis strictly prohibited and may be unlawful.
LEGAL: Skiline Media GmbH - Managing Director: Michael Saringer
Re: Join between 2 tables always executes a sequential scan on the larger table
From
Vasilis Ventirozos
Date:
Hello Dieter,
If you are asking more than about 20% of the rows the optimizer will choose to do a seq scan and actually that's the right thing to do. On the second example of yours the rows here less and that's why it chose to go with the index.
If you are asking more than about 20% of the rows the optimizer will choose to do a seq scan and actually that's the right thing to do. On the second example of yours the rows here less and that's why it chose to go with the index.
you can force an index scan by changing the optimizer parameters as other guys already mentioned
On Wed, Apr 3, 2013 at 11:18 AM, Dieter Rehbein <dieter.rehbein@skiline.cc> wrote:
Hi Igor,
thanks for the reply. The sequential scan on user_2_competition wasn't my main-problem. What really suprised me was the sequential scan on table user, which is a sequential scan over one million rows.
Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
Hash Cond: ((uc.user_id)::text = (u.id)::text)
-> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691 rows=41333 loops=1)
Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
Rows Removed by Filter: 80684
-> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
Buckets: 2048 Batches: 128 Memory Usage: 589kB
-> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673 loops=1) <-- This sequential scan is strange.
IMHO the reason for the sequential scan on user is, that it is faster than an index-scan for 41333 rows. I've tried the same query using a different competition id with much less participants (about 1700). That query has a query plan as expected:
Nested Loop Left Join (cost=0.00..21385.59 rows=1684 width=42) (actual time=1.317..147.781 rows=1757 loops=1)
-> Seq Scan on user_2_competition uc (cost=0.00..7026.93 rows=1684 width=33) (actual time=1.262..92.339 rows=1757 loops=1)
Filter: ((competition_id)::text = '3cc1cb963b988f12013bc737b4590001'::text)
-> Index Scan using user_pkey on "user" u (cost=0.00..8.51 rows=1 width=42) (actual time=0.030..0.031 rows=1 loops=1757)
Index Cond: ((id)::text = (uc.user_id)::text)
Total runtime: 148.068 ms
regards
Dieter
Am 02.04.2013 um 16:55 schrieb Igor Neyman <ineyman@perceptron.com>:
From: Dieter Rehbein [mailto:dieter.rehbein@skiline.cc]
Sent: Tuesday, April 02, 2013 4:52 AM
To: pgsql-performance@postgresql.org
Subject: Join between 2 tables always executes a sequential scan on the larger table
Hi everybody,
in a project I have a performance problem, which I (and my colleagues) don't understand. It's a simple join between 2 of 3 tables:
table-1: user (id, user_name, ...). This table has about 1 million rows (999673 rows)
table-2: competition (57 rows)
table-3: user_2_competition. A relation between user and competition. This table has about 100.000 rows
The query is a join between table user_2_competition and user and looks like this:
select u.id, u.user_name
from user_2_competition uc
left join "user" u on u.id = uc.user_id
where uc.competition_id = '3cc1cb9b3ac132ad013ad01316040001'
The query returns the ID and user_name of all users participating in a competition.
What I don't understand: This query executes a sequential scan on user!
The tables have the following indexes:
user_2_competition: there is an index on user_id and an index on competition_id (competition_id is a VARCHAR(32) containing UUIDs)
user: id is the primary key and has therefore a unique index (the ID is a VARCHAR(32), which contains UUIDs).
The database has just been restored from a backup, I've executed ANALYZE for both tables.
The output of explain analyze (Postgres 9.2.3):
Hash Left Join (cost=111357.64..126222.29 rows=41396 width=42) (actual time=1982.543..2737.331 rows=41333 loops=1)
Hash Cond: ((uc.user_id)::text = (u.id)::text)
-> Seq Scan on user_2_competition uc (cost=0.00..4705.21 rows=41396 width=33) (actual time=0.019..89.691 rows=41333 loops=1)
Filter: ((competition_id)::text = '3cc1cb9b3ac132ad013ad01316040001'::text)
Rows Removed by Filter: 80684
-> Hash (cost=90074.73..90074.73 rows=999673 width=42) (actual time=1977.604..1977.604 rows=999673 loops=1)
Buckets: 2048 Batches: 128 Memory Usage: 589kB
-> Seq Scan on "user" u (cost=0.00..90074.73 rows=999673 width=42) (actual time=0.004..1178.827 rows=999673 loops=1)
Total runtime: 2740.723 ms
I expected to see an index-scan on user_2_competition with a hash join to user, not a sequential scan on user. I've tried this with Postgres 9.1 and 9.2.3).
Any ideas, what's going on here?
With EXPLAIN ANALYZE I can see, which query plan Postgres is using. Is there any way to find out, WHY postgres uses this query plan?
best regards
Dieter
-----------------------------------------------
Dieter,
why do you think index-scan on user_2_competition would be better?
Based on huge number of rows returned (41333 out of total ~120000 in the table) from this table optimizer decided that Seq Scan is better than index scan.
You don't show QUERY TUNING parameters from Postgresql.conf, are they default?
Playing with optimizer parameters (lowering random_page_cost, lowering cpu_index_tuple_cost , increasing effective_cache_size, or just setting enable_seqscan = off), you could try to force "optimizer" to use index, and see if you are getting better results.
Regards,
Igor Neyman
Happy Skiing!
Dieter Rehbein
Software Architect | dieter.rehbein@skiline.cc
Skiline Media GmbH
Lakeside B03
9020 Klagenfurt, Austria
fon: +43 463 249445-800
fax: +43 463 249445-102
"Erlebe Skifahren neu!"
CONFIDENTIALITY: This e-mail and any attachments are confidential and may also be privileged. If you are not the designated recipient, please notify the sender immediately by reply e-mail and destroy all copies (digital and paper). Any unauthorized disclosure, distribution, copying, storage or use of the information contained in this e-mail or any attachments is strictly prohibited and may be unlawful.
LEGAL: Skiline Media GmbH - Managing Director: Michael Saringer
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance