Re: Creating Index

> You do realize that extract returns a double precision value not an
> integer, and it's probably not going to be willing to push clauses down
> through the union where the types are different .
>

Argh! I didn't noticed that. Thanks for the reminder.

Let's do not consider table2 and view1 for this moment and focus only on
table1.
Table1 in my original post was incorrect. Please forgive me! (I posted it
midnight when my head was not clear and tried to make my case simple for
understanding.) The correct one is:

CREATE TABLE table1
( id VARCHAR(20) PRIMARY KEY,
d DATE,
amount INTEGER
);
CREATE INDEX itable1 ON table1 (d);

EXPLAIN ANALYZE SELECT COUNT(*) FROM table1 WHERE EXTRACT(YEAR FROM d) >=
2001.0 AND EXTRACT(MONTH FROM d) >= 1.;

takes 630 msec on my AMD 450MHz machine. While

EXPLAIN ANALYZE SELECT COUNT(*) FROM table1 WHERE d >= '2001-1-1';

takes only 114 msec.
------------------
Aggregate (cost=535.20..535.20 rows=1 width=0) (actual
time=625.10..625.11 rows=1 loops=1)
-> Seq Scan on table1 (cost=0.00..532.58 rows=1048 width=0) (actual
time=14.84..605.85 rows=3603 loops=1)
Filter: ((date_part('year'::text, f2) > 2001::double precision)
AND (date_part('month'::text, f2) >=
1::double precision))
Total runtime: 626.61 msec

-----------------------
Aggregate (cost=464.12..464.12 rows=1 width=0) (actual
time=114.28..114.28 rows=1 loops=1)
-> Seq Scan on table1 (cost=0.00..461.86 rows=902 width=0) (actual
time=10.71..102.99 rows=3603 loops=1)
Filter: (f2 >= '2002-01-01'::date)
Total runtime: 114.50 msec

Does the first query perform sequential scan?
If a composit index (year,month) derived from column "d" helps and is
available, then someone please show me how to build that index like:

CREATE INDEX i1 ON table1 <EXTRACT(YEAR FROM d)::TEXT || EXTRACT(MONTH
FROM d)::TEXT>

Is creating a function that eats DATE as argument to build that index my
only solution?

Best Regards,

CN

--
http://www.fastmail.fm - The professional email service