Skip site navigation (1) Skip section navigation (2)

Re: query plan with index having a btrim is different for strings of different length

From: "Vladimir Sitnikov" <sitnikov(dot)vladimir(at)gmail(dot)com>
To: "Richard Yen" <dba(at)richyen(dot)com>
Cc: "Robert Haas" <robertmhaas(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: query plan with index having a btrim is different for strings of different length
Date: 2008-12-10 22:36:51
Message-ID: 1d709ecc0812101436s1b2b53fdp64620af6af4f9d25@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-performance
>
> 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

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2008-12-11 00:08:28
Subject: Re: query plan with index having a btrim is different for strings of different length
Previous:From: Richard YenDate: 2008-12-10 22:04:57
Subject: Re: query plan with index having a btrim is different for strings of different length

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group