Join between 2 tables always executes a sequential scan on the larger table

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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);




[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux