Search Postgresql Archives

Re: Postgres 8.3 vs. 8.4 - Query plans and performance

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Mon, Mar 14, 2011 at 9:48 AM, Jo <jl.news@xxxxxxxxxxx> wrote:
> I set the work_mem to 100MB and the shared buffers are 2 GB
>
> The query plans are long and complex. I send the beginning of the
> two plans. Hope this helps to understand the differences.
> I assume the join strategy in 8.3 differs from the one in 8.4.
>
>
> *************************************
> The beginning of the 8.4:
> *************************************
> "Seq Scan on relations  (cost=0.00..1502557856.52 rows=332613 width=24)"
> "  Filter: (((SubPlan 36) OR (SubPlan 37)) AND (SubPlan 38))"
> "  SubPlan 1"
> "    ->  Index Scan using idx_relation_tags_relation_id on relation_tags
>  (cost=0.00..8.97 rows=1 width=0)"
> "          Index Cond: (relation_id = $0)"
> "          Filter: ((k ~~* 'name'::text) AND (v !~~* ''::text))"
> "  SubPlan 2"


well, regardless of the version, you're doing a gazillion sequential
scans on relation tags.  This looks like the primary culprit (I had to
look up the ~~* operator...it's 'ilike'):
(
  (k ~~* 'boundary'::text) OR
  (
    (k ~~* 'type'::text)
    AND (v ~~* 'boundary'::text)
    AND (relation_id = $0)
  )
)

1. do we really. really need to be using ~~* here? how about '~~' (like) or '='
2. can we see definition and indexes on relation_tags?  In particular,
have you considered an index on (k,v,relation_id), or maybe one on
(relation_id, v, k) and one on k?
3. can we see the source query?

merlin

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux