From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Anton Bushmelev <djeday84@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: optimization join on random value
Date: Mon, 04 May 2015 00:28:46 +0300
Message-ID: <5546930E.5080201@iki.fi> (raw)
In-Reply-To: <CAK0X_okQKj8C9FO6LMOhMmhCdsaTTpB2aqa26TvWVFoA53-HhQ@mail.gmail.com>
References: <CAK0X_okQKj8C9FO6LMOhMmhCdsaTTpB2aqa26TvWVFoA53-HhQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 05/04/2015 12:23 AM, Anton Bushmelev wrote:
> Hello guru of postgres, it's possoble to tune query with join on random
> string ?
> i know that it is not real life example, but i need it for tests.
>
> soe=# explain
> soe-# SELECT ADDRESS_ID,
> soe-# CUSTOMER_ID,
> soe-# DATE_CREATED,
> soe-# HOUSE_NO_OR_NAME,
> soe-# STREET_NAME,
> soe-# TOWN,
> soe-# COUNTY,
> soe-# COUNTRY,
> soe-# POST_CODE,
> soe-# ZIP_CODE
> soe-# FROM ADDRESSES
> soe-# WHERE customer_id = trunc( random()*45000) ;
> QUERY
> PLAN
> -------------------------------------------------------------------------------------------
> Seq Scan on addresses (cost=0.00..165714.00 rows=22500 width=84)
> Filter: ((customer_id)::double precision = trunc((random() *
> 45000::double precision)))
> (2 rows)
>
There are two problems here that prohibit the index from being used:
1. random() is volatile, so it's recalculated for each row.
2. For the comparison, customer_id is cast to a float, and the index is
on the bigint value.
To work around the first problem, put the random() call inside a
subquery. And for the second problem, cast to bigint.
SELECT ... FROM addresses
WHERE customer_id = (SELECT random()*45000)::bigint
- Heikki
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
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: hlinnaka@iki.fi, djeday84@gmail.com
Subject: Re: optimization join on random value
In-Reply-To: <5546930E.5080201@iki.fi>
* 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