From: Matteo Beccati <php@beccati.com>
To: pgsql-performance@postgresql.org
Subject: ltree <@ operator selectivity causes very slow plan
Date: Fri, 29 Jul 2005 18:50:44 +0200
Message-ID: <42EA5E64.6050209@beccati.com> (raw)
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.comhttp://phppgads.com
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: php@beccati.com
Subject: Re: ltree <@ operator selectivity causes very slow plan
In-Reply-To: <42EA5E64.6050209@beccati.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