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

ltree <@ operator selectivity causes very slow plan

From: Matteo Beccati <php(at)beccati(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: ltree <@ operator selectivity causes very slow plan
Date: 2005-07-29 16:50:44
Message-ID: 42EA5E64.6050209@beccati.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi,

I'm happily using ltree since a long time, but I'm recently having 
troubles because of ltree <@ operator selectivity that is causing very 
bad planner choices.

An example of slow query is:

SELECT
   batch_id,
   b.t_stamp AS t_stamp,
   objects,
   CASE WHEN sent IS NULL THEN gw_batch_sent(b.batch_id) ELSE sent END 
AS sent
FROM
   gw_users u JOIN gw_batches b USING (u_id)
WHERE
   u.tree <@ '1041' AND
   b.t_stamp >= 'today'::date - '7 days'::interval AND
   b.t_stamp < 'today'
ORDER BY
   t_stamp DESC;

I've posted the EXPLAIN ANALYZE output here for better readability: 
http://rafb.net/paste/results/NrCDMs50.html

As you may see, disabling nested loops makes the query lightning fast.


The problem is caused by the fact that most of the records of gw_users 
match the "u.tree <@ '1041'" condition:

SELECT COUNT(*) FROM gw_users;
  count
-------
   5012

SELECT COUNT(*) FROM gw_users WHERE tree <@ '1041';
  count
-------
   4684

Is there anything I can do apart from disabling nested loops?


Best regards
--
Matteo Beccati
http://phpadsnew.com
http://phppgads.com

pgsql-performance by date

Next:From: Luke LonerganDate: 2005-07-29 17:11:10
Subject: Re: Performance problems testing with Spamassassin
Previous:From: Josh BerkusDate: 2005-07-29 16:47:03
Subject: Re: Performance problems testing with Spamassassin

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