Re: Odd misprediction - Mailing list pgsql-performance

On Fri, Sep 16, 2011 at 17:50, Claudio Freire <klaussfreire@gmail.com> wrote:
> It's not an issue for me (it's not really impacting performance), but
> since it was odd I thought I might ask.
>
> I have this supermegaquery:
>
> SELECT
>       t.date AS status_date, lu.id AS memberid, lu.username AS
> username, u.url AS url, ub.url_pattern AS urlpattern, lu.email AS
> email,
>       lu.birth_date AS birthdate, lu.creation_date AS creationdate,
> s.name AS state, co.name AS country,
>       opd.survey_id AS originalSurvey, c.name AS city , lu.confirmed
> AS confirmed , pd.name AS action , sd.duration AS loi
> FROM   tracks t
>       LEFT JOIN surveyduration_v sd
>       ON     sd.member_id = t.member_id
>       AND    sd.survey_id = 5936
>       INNER JOIN all_users_v lu
>       ON     lu.id              = t.member_id
>       AND    lu.panel_source_id = 1
>       LEFT JOIN track_status ts
>       ON     ts.id = t.track_status_id
>       LEFT JOIN partners p
>       ON     p.id = t.partner_id
>       LEFT JOIN urls u
>       ON     u.id        = t.url_id
>       AND    u.survey_id = 5936
>       LEFT JOIN url_batchs ub
>       ON     u.url_batch_id = ub.id
>       LEFT JOIN states s
>       ON     lu.state_id = s.id
>       LEFT JOIN cities c
>       ON     lu.city_id = c.id
>       LEFT JOIN countries co
>       ON     lu.country_id = co.id
>       LEFT JOIN partner_deliveries pd
>       ON     pd.id                  = t.partner_delivery_id
>       AND    t.partner_id IS NOT NULL
>       LEFT JOIN partner_deliveries opd
>       ON     opd.id = pd.originator_id
> WHERE  t.survey_id   = 5936
> AND    t.track_status_id IN (5)
>
> With the views
>
> CREATE OR REPLACE VIEW surveyduration_v AS
>  SELECT date_part('epoch'::text, t.date - tl2.date) / 60::double
> precision AS duration, t.member_id, t.survey_id
>   FROM tracks t
>   JOIN track_logs tl2 ON t.id = tl2.track_id
>  WHERE tl2.track_status_id = 8 AND t.track_status_id = 7;
>
> CREATE OR REPLACE VIEW all_users_v AS
>         SELECT 1 AS panel_source_id, livra_users.id,
> livra_users.birth_date, livra_users.creation_date, livra_users.email,
> livra_users.first_name, livra_users.last_name, livra_users.username,
> livra_users.image_link, livra_users.confirmed,
> livra_users.is_panelist, livra_users.unregistered, livra_users.reason,
> livra_users.privacy, livra_users.sex, livra_users.site,
> livra_users.country_id, livra_users.state_id, livra_users.city_id,
> livra_users.last_activity_date, livra_users.partner_id,
> livra_users.survey_id, livra_users.panelist_update,
> livra_users.panelist_percentage
>           FROM livra_users
> UNION ALL
>         SELECT 2 AS panel_source_id, - external_users.id AS id,
> NULL::timestamp without time zone AS birth_date,
> external_users.creation_date, external_users.email, NULL::character
> varying AS first_name, NULL::character varying AS last_name,
> external_users.username, NULL::character varying AS image_link, true
> AS confirmed, external_users.is_panelist, false AS unregistered,
> NULL::integer AS reason, 0 AS privacy, NULL::integer AS sex,
> external_users.site, external_users.country_id, NULL::integer AS
> state_id, NULL::integer AS city_id, NULL::timestamp without time zone
> AS last_activity_date, NULL::integer AS partner_id,
> external_users.survey_id, NULL::bigint AS panelist_update,
> NULL::smallint AS panelist_percentage
>           FROM external_users;
>
> Server is 9.0.3 running on linux
>
> The BIG tables are tracks, track_logs and urls, all > 30M rows.
>
> One detail that could be related is that tracks.member_id is an
> undeclared (denoramlized) foreign key to livra_users.
>
> The resulting plan is:
>
> "Hash Left Join  (cost=51417.93..974563.27 rows=2241518 width=1276)"
> "  Hash Cond: ("*SELECT* 1".country_id = co.id)"
> "  ->  Hash Left Join  (cost=51415.40..941722.50 rows=2241518 width=1271)"
> "        Hash Cond: ("*SELECT* 1".state_id = s.id)"
> "        ->  Hash Left Join  (cost=51373.45..910859.68 rows=2241518 width=1263)"
> "              Hash Cond: (t.partner_delivery_id = pd.id)"
> "              Join Filter: (t.partner_id IS NOT NULL)"
> "              ->  Hash Left Join  (cost=32280.78..854175.26
> rows=2241518 width=1256)"
> "                    Hash Cond: ("*SELECT* 1".city_id = c.id)"
> "                    ->  Hash Join  (cost=24183.20..792841.63
> rows=2241518 width=1249)"
> "                          Hash Cond: ("*SELECT* 1".id = t.member_id)"
> "                          ->  Append  (cost=0.00..148254.38
> rows=3008749 width=168)"
> "                                ->  Subquery Scan on "*SELECT* 1"
> (cost=0.00..140223.96 rows=3008748 width=168)"
> "                                      ->  Seq Scan on livra_users
> (cost=0.00..110136.48 rows=3008748 width=168)"
> "                                ->  Subquery Scan on "*SELECT* 2"
> (cost=0.00..8030.42 rows=1 width=60)"
> "                                      ->  Result  (cost=0.00..8030.41
> rows=1 width=60)"
> "                                            One-Time Filter: false"
> "                                            ->  Seq Scan on
> external_users  (cost=0.00..8030.41 rows=1 width=60)"
> "                          ->  Hash  (cost=24181.34..24181.34 rows=149
> width=188)"
> "                                ->  Hash Left Join
> (cost=21650.42..24181.34 rows=149 width=188)"
> "                                      Hash Cond: (u.url_batch_id = ub.id)"
> "                                      ->  Nested Loop Left Join
> (cost=20828.08..23355.84 rows=149 width=115)"
> "                                            ->  Merge Left Join
> (cost=20828.08..20841.04 rows=149 width=44)"
> "                                                  Merge Cond:
> (t.member_id = t.member_id)"
> "                                                  ->  Sort
> (cost=435.90..436.27 rows=149 width=32)"
> "                                                        Sort Key: t.member_id"
> "                                                        ->  Index
> Scan using idx_tracks_survey_id_track_status_id on tracks t
> (cost=0.00..430.52 rows=149 width=32)"
> "                                                              Index
> Cond: ((survey_id = 5936) AND (track_status_id = 5))"
> "                                                  ->  Sort
> (cost=20392.18..20398.28 rows=2440 width=20)"
> "                                                        Sort Key: t.member_id"
> "                                                        ->  Nested
> Loop  (cost=0.00..20254.90 rows=2440 width=20)"
> "                                                              ->
> Index Scan using idx_tracks_survey_id_track_status_id on tracks t
> (cost=0.00..2010.03 rows=712 width=20)"
> "
> Index Cond: ((survey_id = 5936) AND (track_status_id = 7))"
> "                                                              ->
> Index Scan using idx_track_logs_track_id on track_logs tl2
> (cost=0.00..25.59 rows=3 width=16)"
> "
> Index Cond: (tl2.track_id = t.id)"
> "
> Filter: (tl2.track_status_id = 8)"
> "                                            ->  Index Scan using
> urls_pkey on urls u  (cost=0.00..16.87 rows=1 width=87)"
> "                                                  Index Cond: (u.id =
> t.url_id)"
> "                                                  Filter: (u.survey_id = 5936)"
> "                                      ->  Hash  (cost=637.15..637.15
> rows=14815 width=81)"
> "                                            ->  Seq Scan on
> url_batchs ub  (cost=0.00..637.15 rows=14815 width=81)"
> "                    ->  Hash  (cost=4578.37..4578.37 rows=281537 width=15)"
> "                          ->  Seq Scan on cities c
> (cost=0.00..4578.37 rows=281537 width=15)"
> "              ->  Hash  (cost=18270.17..18270.17 rows=65799 width=19)"
> "                    ->  Hash Left Join  (cost=8842.48..18270.17
> rows=65799 width=19)"
> "                          Hash Cond: (pd.originator_id = opd.id)"
> "                          ->  Seq Scan on partner_deliveries pd
> (cost=0.00..8019.99 rows=65799 width=19)"
> "                          ->  Hash  (cost=8019.99..8019.99 rows=65799 width=8)"
> "                                ->  Seq Scan on partner_deliveries
> opd  (cost=0.00..8019.99 rows=65799 width=8)"
> "        ->  Hash  (cost=24.20..24.20 rows=1420 width=16)"
> "              ->  Seq Scan on states s  (cost=0.00..24.20 rows=1420 width=16)"
> "  ->  Hash  (cost=1.68..1.68 rows=68 width=13)"
> "        ->  Seq Scan on countries co  (cost=0.00..1.68 rows=68 width=13)"
>
> The curious bit is the rowcount (2241518) which is grossly
> misestimated. It gets to that rowcount when joining the all_users_v
> view with tracks, both partial results are estimated at ~150 rows
> (more or less on target), it's a join of int PK to int column, so I
> cannot imagine how that join could result in 2M rows, what is pg
> thinking to get to that number?
>
> Even a full cross product couldn't get that high.
>
> Performance isn't impacted, the plan, even with the misestimation, is
> near optimal. But I can imagine this kind of misestimation wreaking
> havoc in other situations.

Looks like these reports could be related:

http://archives.postgresql.org/pgsql-performance/2011-08/msg00248.php
http://archives.postgresql.org/pgsql-hackers/2011-08/msg01388.php

Tom Lane tracked these down to a likely cause, but AFAICT this has not
been fixed yet.

Regards,
Marti