Date: Mon, 5 Jul 2010 09:47:07 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Jens Hoffrichter <jens.hoffrichter@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: SeqScans on boolen values / How to speed this up?
Message-ID: <20100705134707.GK21875@tamriel.snowman.net>
References: <AANLkTimprnFkqv_Z9uDuwW1u2iok75Zn1N1jMiYI8fWy@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature"; boundary="GuUIMmKHequvRq+F"
Content-Disposition: inline
In-Reply-To: <AANLkTimprnFkqv_Z9uDuwW1u2iok75Zn1N1jMiYI8fWy@mail.gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)


--GuUIMmKHequvRq+F
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Jens,

* Jens Hoffrichter (jens.hoffrichter@gmail.com) wrote:
> I'm just curious if there is any way to improve the performance of
> those queries. I'm seeing SeqScans in the EXPLAIN ANALYZE, but nothing
> I have done yet has removed those.

SeqScans aren't necessairly bad.  Also, providing your postgresql.conf
parameters would be useful in doing any kind of analysis work like this.

For starters, why are you using left joins for these queries?  When you
use a left-join and then have a filter on the right-hand table that
requires it to be non-null, you're causing it to be an inner join
anyway.  Fixing that might change/improve the plans you're getting.

> The statements and query plans are:
>=20
> ---- Query 1 -----
>=20
> explain analyze SELECT
> n.name_short,n.flag,n.nation_id,n.urlidentifier,count(p.person_id) as
> athletes from nations n left join persons p on n.nation_id =3D
> p.nation_id left join efclicences e on p.person_id =3D e.person_id where
> continent =3D 'eu' and  p.deleted =3D false and p.inactive =3D false and
> e.fencer =3D true group by
> n.name_short,n.flag,n.nation_id,n.urlidentifier order by n.name_short;

Alright, for this one, you're processing 144k rows in persons
up into the aggregate, how big is the table?  If it's anything less than
1M, seqscanning that is almost certainly the fastest way.  You could
*test* that theory by disabling seqscans and running the query again for
the timing.  If it's faster, then you probably need to adjust some PG
parameters (eg: effective_cache_size, maybe random_page_cost) for your
system.

>                                                                    QUERY =
PLAN
> -------------------------------------------------------------------------=
------------------------------------------------------------------------
>  Sort  (cost=3D9997.21..9997.32 rows=3D44 width=3D33) (actual
> time=3D872.000..872.000 rows=3D44 loops=3D1)
>    Sort Key: n.name_short
>    Sort Method:  quicksort  Memory: 28kB
>    ->  HashAggregate  (cost=3D9995.45..9996.01 rows=3D44 width=3D33) (act=
ual
> time=3D872.000..872.000 rows=3D44 loops=3D1)
>          ->  Hash Join  (cost=3D5669.49..9611.83 rows=3D30690 width=3D33)
> (actual time=3D332.000..720.000 rows=3D142240 loops=3D1)
>                Hash Cond: (e.person_id =3D p.person_id)
>                ->  Seq Scan on efclicences e  (cost=3D0.00..2917.29
> rows=3D143629 width=3D8) (actual time=3D0.000..80.000 rows=3D143629 loops=
=3D1)
>                      Filter: fencer
>                ->  Hash  (cost=3D5285.87..5285.87 rows=3D30690 width=3D33)
> (actual time=3D332.000..332.000 rows=3D142240 loops=3D1)
>                      ->  Hash Join  (cost=3D7.10..5285.87 rows=3D30690
> width=3D33) (actual time=3D0.000..256.000 rows=3D142240 loops=3D1)
>                            Hash Cond: (p.nation_id =3D n.nation_id)
>                            ->  Seq Scan on persons p
> (cost=3D0.00..4438.29 rows=3D142288 width=3D16) (actual time=3D0.000..112=
=2E000
> rows=3D142418 loops=3D1)
>                                  Filter: ((NOT deleted) AND (NOT inactive=
))
>                            ->  Hash  (cost=3D6.55..6.55 rows=3D44
> width=3D25) (actual time=3D0.000..0.000 rows=3D44 loops=3D1)
>                                  ->  Seq Scan on nations n
> (cost=3D0.00..6.55 rows=3D44 width=3D25) (actual time=3D0.000..0.000 rows=
=3D44
> loops=3D1)
>                                        Filter: ((continent)::text =3D 'eu=
'::text)
>  Total runtime: 880.000 ms
> (17 rows)
>=20
> --- Query 2 ---
> explain analyze SELECT persons.person_id AS persons_person_id FROM
> persons LEFT OUTER JOIN indexing_persons ON persons.person_id =3D
> indexing_persons.person_id WHERE  indexing_persons.person_id IS  NULL
> OR persons.modified > indexing_persons.indexed ORDER  BY
> persons.modified DESC LIMIT 1000;

For this one, you might try indexing persons.modified and
indexing_persons.indexed and see if that changes things.

> -------------------------------------------------------------------------=
------------------------------------------------------------------------
>  Limit  (cost=3D17755.23..17757.73 rows=3D1000 width=3D16) (actual
> time=3D372.000..372.000 rows=3D0 loops=3D1)
>    ->  Sort  (cost=3D17755.23..17994.61 rows=3D95753 width=3D16) (actual
> time=3D372.000..372.000 rows=3D0 loops=3D1)
>          Sort Key: persons.modified
>          Sort Method:  quicksort  Memory: 25kB
>          ->  Hash Left Join  (cost=3D4313.44..12505.20 rows=3D95753
> width=3D16) (actual time=3D372.000..372.000 rows=3D0 loops=3D1)
>                Hash Cond: (persons.person_id =3D indexing_persons.person_=
id)
>                Filter: ((indexing_persons.person_id IS NULL) OR
> (persons.modified > indexing_persons.indexed))
>                ->  Seq Scan on persons  (cost=3D0.00..4438.29
> rows=3D143629 width=3D16) (actual time=3D0.000..56.000 rows=3D143629 loop=
s=3D1)
>                ->  Hash  (cost=3D2534.86..2534.86 rows=3D142286 width=3D1=
6)
> (actual time=3D140.000..140.000 rows=3D143629 loops=3D1)
>                      ->  Seq Scan on indexing_persons
> (cost=3D0.00..2534.86 rows=3D142286 width=3D16) (actual time=3D0.000..72.=
000
> rows=3D143629 loops=3D1)
>  Total runtime: 372.000 ms
> (11 rows)

	Thanks,

		Stephen

--GuUIMmKHequvRq+F
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (GNU/Linux)

iEYEARECAAYFAkwx4lsACgkQrzgMPqB3kiiTIwCfUXjHnEaAj8iFJDFk/ehEsR6h
95UAn3Q+Qmdpv0NdioxihFgoMQwZPpGP
=ewi0
-----END PGP SIGNATURE-----

--GuUIMmKHequvRq+F--