From: Vladimir Sitnikov <sitnikov.vladimir@gmail.com>
To: Richard Yen <dba@richyen.com>
Cc: Robert Haas <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: query plan with index having a btrim is different for strings of different length
Date: Thu, 11 Dec 2008 01:36:51 +0300
Message-ID: <1d709ecc0812101436s1b2b53fdp64620af6af4f9d25@mail.gmail.com> (raw)
In-Reply-To: <129E8D79-341B-411A-83B7-E2B509A7B6E2@richyen.com>
References: <751F49DC-1142-4E9C-95C8-BC3C229B3F0B@richyen.com>
<22937.1228865243@sss.pgh.pa.us>
<E8AE9590-4E30-49C0-B11F-D7131009A095@richyen.com>
<603c8f070812101139p189cc828hef8702f37f952b6d@mail.gmail.com>
<129E8D79-341B-411A-83B7-E2B509A7B6E2@richyen.com>
>
> tii=# explain analyze SELECT m_object_paper.id FROM m_object_paper,
> m_assignment WHERE m_object_paper.assignment = m_assignment.id AND
> m_object_paper.owner=-1 AND m_assignment.class = 2450798 AND
> lower(btrim(x_firstname)) = lower(btrim('Jordan')) and
> lower(btrim(x_lastname)) = lower(btrim('Smith'));
Is there an index on "m_object_paper.assignment"? It could solve the
problem.
With current indices on "btrim(last_name)" and "owner" you are just throwing
the rows away (you have 521382 rows with "smith", 15494737 with owner=-1 and
only 58 of them have both "smith"/"jordan" and -1).
Consider creating index on m_object_paper using
btree(lower(btrim(x_lastname))) where owner=-1; (it might add firstname
column there as per Tom's suggestion)
Or just index on (owner, lower(...)) if you have other queries with
different values for owner.
One more point that could improve bitmap scans is greater value for
work_mem. You'll need 8*15494737 ~ 130Mb == 130000 for work_mem (however,
that is way too high unless you have lots of RAM and just couple of active
database sessions)
Regards,
Vladimir Sitnikov
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sitnikov.vladimir@gmail.com, dba@richyen.com, robertmhaas@gmail.com
Subject: Re: query plan with index having a btrim is different for strings of different length
In-Reply-To: <1d709ecc0812101436s1b2b53fdp64620af6af4f9d25@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox