pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: dld <dld@casema.nl>
To: pgsql-general@lists.postgresql.org
Subject: Re: Indexing fragments of a column's value ?
Date: Tue, 14 Nov 2023 16:49:46 +0100
Message-ID: <f81482c6-bb3a-40d6-bf31-bf0f7ffdf323@casema.nl> (raw)


You can index on expressions, and these will be recognised by the query 
generator.

drop schema tmp CASCADE;
create schema tmp;
set search_path = tmp;

CREATE TABLE bagger
         ( eight CHAR(8) NOT NULL PRIMARY KEY
         , more text
         );
CREATE INDEX bagger_idx_12 ON bagger (substr(eight,1,2));
CREATE INDEX bagger_idx_34 ON bagger (substr(eight,3,2));
CREATE INDEX bagger_idx_58 ON bagger (substr(eight,5,4));

INSERT INTO bagger(eight, more)
SELECT translate(to_hex( gs), ' ' , '0')
         , gs::text
FROM generate_series(0,4000000000, 64999) gs
         ;

VACUUM ANALYZE bagger;

EXPLAIN ANALYZE
SELECT * FROM bagger
WHERE 1=1
         -- AND eight >= '00' AND eight < '05'
         AND substr(eight, 1,2) >= '30' AND substr(eight,1,2) < '05'
         AND substr(eight, 3,2) >= '90' AND substr(eight,3,2) < 'A5'
         AND substr(eight, 5,4) >= '3333' AND substr(eight,5,4) < '4444'
         ;

/*** The optimiser is smart enough to ignore one of the indexes. */
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on bagger  (cost=41.46..59.62 rows=1 width=19) 
(actual time=0.049..0.056 rows=0 loops=1)
    Recheck Cond: ((substr((eight)::text, 1, 2) >= '30'::text) AND 
(substr((eight)::text, 1, 2) < '05'::text) AND (substr((eight)::text, 3, 
2) >= '90'::text) AND (substr((eight)::text, 3, 2) < 'A5'::text))
    Filter: ((substr((eight)::text, 5, 4) >= '3333'::text) AND 
(substr((eight)::text, 5, 4) < '4444'::text))
    ->  BitmapAnd  (cost=41.46..41.46 rows=16 width=0) (actual 
time=0.042..0.047 rows=0 loops=1)
          ->  Bitmap Index Scan on bagger_idx_12 (cost=0.00..4.47 
rows=308 width=0) (actual time=0.039..0.039 rows=0 loops=1)
                Index Cond: ((substr((eight)::text, 1, 2) >= '30'::text) 
AND (substr((eight)::text, 1, 2) < '05'::text))
          ->  Bitmap Index Scan on bagger_idx_34 (cost=0.00..36.74 
rows=3205 width=0) (never executed)
                Index Cond: ((substr((eight)::text, 3, 2) >= '90'::text) 
AND (substr((eight)::text, 3, 2) < 'A5'::text))
  Planning Time: 5.487 ms
  Execution Time: 0.310 ms
(10 rows)


HTH,

AvK






view thread (5+ messages)

Message-ID: <f81482c6-bb3a-40d6-bf31-bf0f7ffdf323@casema.nl>
Permalink:  ../f81482c6-bb3a-40d6-bf31-bf0f7ffdf323@casema.nl/
Also on:    postgresql.org/message-id/f81482c6-bb3a-40d6-bf31-bf0f7ffdf323@casema.nl

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-general@postgresql.org
  Cc: dld@casema.nl
  Subject: Re: Indexing fragments of a column's value ?
  In-Reply-To: <f81482c6-bb3a-40d6-bf31-bf0f7ffdf323@casema.nl>
  References: <f81482c6-bb3a-40d6-bf31-bf0f7ffdf323@casema.nl>

* 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