> Greets,
>
> I'm trying to figure out why the following SELECT has become slow
> (hardware,
> code changes, etc) and would appreciate any comments on interpreting the
> EXPLAIN ANALYZE output. It *used* to take a few seconds at most, but not
> anymore... In figuring out which part is taking so long, what's the
> important
> bit to examine (besides making sure indexes are being used)? Presumably
> "actual time="?
>
> If I can identify which index is taking the longest from the output below,
> then I can look at moving it to a faster TABLESPACE or something.
>
>
> explain analyze
> SELECT pl.sss, pl.did, pl.lid, pr.rank, plc.obl
> FROM plink pl LEFT JOIN prank pr USING (did)
> LEFT JOIN plink_count plc ON md5(plc.did)=md5(pr.did)
> LEFT JOIN snames s ON s.name=pl.sss
> WHERE
> s.bsit=0 AND s.disabled=0 AND
> s.prankignore=0 AND
> pl.lid = lower(E'stuff');
>
See this http://explain.depesz.com/s/THh
There's something very wrong with snames - the planner expects 22 rows but
gets 164147851. Which probably causes a bad plan choice or something like
that. Try to analyze the snames table (and maybe increase the statistics
target on the columns).
regards
Tomas