From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andreas Joseph Krogh <andreak@officenet.no>
Cc: Achilleus Mantzios <achill@matrix.gatewaynet.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: index on to_char(created, 'YYYY') doesn't work
Date: Wed, 15 Jan 2003 10:57:18 -0500
Message-ID: <5114.1042646238@sss.pgh.pa.us> (raw)
In-Reply-To: <200301151504.47092.andreak@officenet.no>
References: <Pine.LNX.4.44.0301151651120.3008-100000@matrix.gatewaynet.com>
<200301151504.47092.andreak@officenet.no>
Andreas Joseph Krogh <andreak@officenet.no> writes:
> create index session_u_idx on session (drus(created)) where username is null;
> select to_char(created, 'IW') as week, count(session_id) from session WHERE
> username IS NULL and drus(created) = '2002' group by week ORDER BY week;
> This table is quite large(900 000 rows) and the query takes several
> minutes to run, which makes the browser timeout.
Is the query actually using the index? It looks like it should, but
there's no substitute for making sure. May we see the output of
EXPLAIN ANALYZE for this query? How does it change if you force
indexscan or force seqscan (by setting enable_seqscan or
enable_indexscan to 0 respectively)?
> Is the use of to_char on the timestamp wrong
I'd be inclined to write cast(extract(year from created) as int) so that
the index key is int rather than varchar --- but this is probably just a
marginal efficiency hack. The real problem is very likely that the
query selects such a large fraction of the table rows that the index
isn't buying you anything.
regards, tom lane
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, andreak@officenet.no, achill@matrix.gatewaynet.com
Subject: Re: index on to_char(created, 'YYYY') doesn't work
In-Reply-To: <5114.1042646238@sss.pgh.pa.us>
* 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