Hi,
i need some help or suggestions for performance increasing on my queries.
My version: PostgreSQL 7.2.1 on i686-pc-linux-gnu, compiled by GCC 2.95.3
My table is:
CREATE TABLE "stat_pages" (
"visit" timestamp with time zone,
"script_id" integer,
"a_id" character(30),
"p_id" character(30),
"m_id" smallint,
"s_id" smallint,
"session_id" character(50),
"action" character(20)
);
This table contains 343554 rows and i have the following index:
CREATE INDEX "stat_pages_m_id_idx" on "stat_pages" using btree ( "m_id" "int2_ops" );
i cannot create an index like this:
CREATE INDEX "stat_pages_datum_idx" on "stat_pages" ( date ("visit") );
The error is about the »iscachable« tag of the index function.
The table is vacuumed full analyzed.
My typical queries are like that:
SELECT count(a_id) AS count
FROM (
SELECT DISTINCT a_id FROM stat_pages
WHERE m_id = '35'
AND visit >= '2002-09-01'
AND visit <= '2002-09-30'
) AS foo;
The explain shows only sequence scans on stat_pages:
EXPLAIN SELECT count(a_id) FROM ( SELECT DISTINCT a_id FROM stat_pages WHERE m_id = '35' AND visit >= '2002-09-01' AND
visit<= '2002-09-30' ) AS foo;
Aggregate (cost=41479.21..41479.21 rows=1 width=34)
-> Subquery Scan foo (cost=40947.80..41430.90 rows=19324 width=34)
-> Unique (cost=40947.80..41430.90 rows=19324 width=34)
-> Sort (cost=40947.80..40947.80 rows=193241 width=34)
-> Seq Scan on stat_pages (cost=0.00..13821.19 rows=193241 width=34
How can i improve the speed? What kind of index could be usefull in this case?
How can query this table to get the result fast?
Any help, documentation pointers or suggestions welcome!
Greetings,
-tb
--
Thomas Beutin tb@laokoon.IN-Berlin.DE
Beam me up, Scotty. There is no intelligent live down in Redmond.