From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | Willy-Bas Loos <willybas(at)gmail(dot)com> |
Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: seqscan for 100 out of 3M rows, index present |
Date: | 2013-06-26 19:07:36 |
Message-ID: | CAOR=d=1gosvUcpxcqEk39ASuxPS2ngBmuP+V+rPjvSJui3=6Rw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
On Wed, Jun 26, 2013 at 9:45 AM, Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:
> Hi,
>
> postgres does a seqscan, even though there is an index present and it should
> be much more efficient to use it.
> I tried to synthetically reproduce it, but it won't make the same choice
> when i do.
> I can reproduce it with a simplified set of the data itself though.
>
> here's the query, and the analyzed plan:
> select count(*)
> from d2
> join g2 on g2.gid=d2.gid
> where g2.k=1942
>
> Aggregate (cost=60836.71..60836.72 rows=1 width=0) (actual
> time=481.526..481.526 rows=1 loops=1)
> -> Hash Join (cost=1296.42..60833.75 rows=1184 width=0) (actual
> time=317.403..481.513 rows=17 loops=1)
> Hash Cond: (d2.gid = g2.gid)
> -> Seq Scan on d2 (cost=0.00..47872.54 rows=3107454 width=8)
> (actual time=0.013..231.707 rows=3107454 loops=1)
But this plan isn't retrieving just a few rows from d2, it's
retreiving 3.1 Million rows.
From | Date | Subject | |
---|---|---|---|
Next Message | Igor Neyman | 2013-06-26 19:08:06 | Re: seqscan for 100 out of 3M rows, index present |
Previous Message | Willy-Bas Loos | 2013-06-26 19:03:41 | Re: seqscan for 100 out of 3M rows, index present |