Re: Slow Query - Postgres 9.2

On 3 March 2016 at 10:33, Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> wrote:

> On 3/2/16, drum(dot)lucas(at)gmail(dot)com <drum(dot)lucas(at)gmail(dot)com> wrote:
> > Hi all...
> >
> > I'm working on a Slow Query. It's faster now (It was 20sec before) but
> > still not good.
> >
> > Can you have a look and see if you can find something?
> > Cheers
> >
> > Query:
> >
> > WITH jobs AS (
> > ...
> > FROM
> > jobs AS job
> > JOIN
> > public.ja_notes AS note
> > ON
> > note.jobid = job.id
> > AND note.note_type IN ('time', 'part')
> > ...
>
> It is the most long part. All query is 8.8sec.
> SeqScan by CTE is 2.8sec! and index scan in ix_notes_jobid_per_type
> 500rows(loops) * 9.878ms!!! = 4.939sec.
>
> Why does it take so long time?
> For example, index scan in ja_customers_pkey is only 0.781 per row...
> 10 times faster!
>
> What definition of the ix_notes_jobid_per_type? Is it bloated?
>
>
> Hi there!

CREATE INDEX
ix_notes_jobid_per_type
ON
ja_notes
(
"jobid",
"note_type"
);

\di+ ix_notes_jobid_per_type

List of relations

Schema | Name | Type | Owner | Table | Size |
Description

--------+-------------------------+-------+----------+----------+--------+-------------

public | ix_notes_jobid_per_type | index | postgres | ja_notes | 484 MB |

it does not seem to be bloated... since the table is 2805 MB