Thread: Optimizing query
Hello!
I have some trouble getting good results from my query.
here is structure
stat_views
id | integer
id_zone | integer
created | timestamp
I have btree index on created and also id and there is 1633832 records in
that table
First of all I have to manualy set seq_scan to OFF because I always get
seq_scan. When i set it to off my explain show:
explain SELECT count(*) as views FROM stat_views WHERE id = 12;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=122734.86..122734.86 rows=1 width=0)
-> Index Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60 rows=40904 width=0)
Index Cond: (id = 12)
But what I need is to count views for some day, so I use
explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
-> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
Filter: (date_part('day'::text, created) = 18::double precision)
How can I make this to use index and speed the query. Now it takes about 12
seconds.
--
Best regards,
Uros mailto:uros@sir-mag.com
Do something like:
CREATE OR REPLACE FUNCTION my_date_part( timestamp) RETURNS DOUBLE precision AS '
DECLARE
mydate ALIAS FOR $1;
BEGIN
return date_part( ''day'', mydate );
END;' LANGUAGE 'plpgsql' IMMUTABLE;
DECLARE
mydate ALIAS FOR $1;
BEGIN
return date_part( ''day'', mydate );
END;' LANGUAGE 'plpgsql' IMMUTABLE;
create index idx_tmp on stat_views( my_date_part( created ) );
or add an extra date_part column to your table which pre-calculates date_part('day', created) and put an index on this.
Cheers
Matthew
--
----- Original Message -----From: UrosSent: Wednesday, November 19, 2003 10:41 AMSubject: [GENERAL] Optimizing queryHello!
I have some trouble getting good results from my query.
here is structure
stat_views
id | integer
id_zone | integer
created | timestamp
I have btree index on created and also id and there is 1633832 records in
that table
First of all I have to manualy set seq_scan to OFF because I always get
seq_scan. When i set it to off my explain show:
explain SELECT count(*) as views FROM stat_views WHERE id = 12;
QUERY PLAN
----------------------------------------------------------------------------------------------------
Aggregate (cost=122734.86..122734.86 rows=1 width=0)
-> Index Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60 rows=40904 width=0)
Index Cond: (id = 12)
But what I need is to count views for some day, so I use
explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
QUERY PLAN
------------------------------------------------------------------------------------
Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
-> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
Filter: (date_part('day'::text, created) = 18::double precision)
How can I make this to use index and speed the query. Now it takes about 12
seconds.
--
Best regards,
Uros mailto:uros@sir-mag.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
_____________________________________________________________________
This e-mail has been scanned for viruses by MCI's Internet Managed Scanning Services - powered by MessageLabs. For further information visit http://www.mci.com
Uros writes:
> explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
>
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
> Filter: (date_part('day'::text, created) = 18::double precision)
Create an index on date_part('day', created). In 7.3 and earlier you need
to create a wrapper function and index that, in 7.4 you can index
arbitrarz expressions directly. The documentation contains more
information about that.
--
Peter Eisentraut peter_e@gmx.net
Uros wrote:
> Hello!
>
> I have some trouble getting good results from my query.
>
> here is structure
>
> stat_views
> id | integer
> id_zone | integer
> created | timestamp
>
>
> I have btree index on created and also id and there is 1633832 records in
> that table
>
> First of all I have to manualy set seq_scan to OFF because I always get
> seq_scan. When i set it to off my explain show:
>
> explain SELECT count(*) as views FROM stat_views WHERE id = 12;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------
> Aggregate (cost=122734.86..122734.86 rows=1 width=0)
> -> Index Scan using stat_views_id_idx on stat_views (cost=0.00..122632.60 rows=40904 width=0)
> Index Cond: (id = 12)
>
> But what I need is to count views for some day, so I use
>
> explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
>
> QUERY PLAN
> ------------------------------------------------------------------------------------
> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
> Filter: (date_part('day'::text, created) = 18::double precision)
>
>
> How can I make this to use index and speed the query. Now it takes about 12
> seconds.
Can you post explain analyze for the same?
Shridhar
Hello Shridhar,
I use Matthew's solution and it works. Query takes only half a second. I
didn't know that i can index function to.
Thanks
Uros
Wednesday, November 19, 2003, 1:23:26 PM, you wrote:
SD> Uros wrote:
>> Hello!
>>
>> I have some trouble getting good results from my query.
>>
>> here is structure
>>
>> stat_views
>> id | integer
>> id_zone | integer
>> created | timestamp
>>
>>
>> I have btree index on created and also id and there is 1633832 records in
>> that table
>>
>> First of all I have to manualy set seq_scan to OFF because I always get
>> seq_scan. When i set it to off my explain show:
>>
>> explain SELECT count(*) as views FROM stat_views WHERE id = 12;
>> QUERY PLAN
>> ----------------------------------------------------------------------------------------------------
>> Aggregate (cost=122734.86..122734.86 rows=1 width=0)
>> -> Index Scan using stat_views_id_idx on stat_views
>> (cost=0.00..122632.60 rows=40904 width=0)
>> Index Cond: (id = 12)
>>
>> But what I need is to count views for some day, so I use
>>
>> explain SELECT count(*) as views FROM stat_views WHERE date_part('day', created) = 18;
>>
>> QUERY PLAN
>> ------------------------------------------------------------------------------------
>> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
>> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984 width=0)
>> Filter: (date_part('day'::text, created) = 18::double precision)
>>
>>
>> How can I make this to use index and speed the query. Now it takes about 12
>> seconds.
SD> Can you post explain analyze for the same?
SD> Shridhar
Greetings all,
Yesterday I upgraded from 7.3 to 7.4 now psql doesn't work! I get the
following error.
psql: relocation error: psql: undefined symbol: get_progname
Any ideas out there?
Rob
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Shridhar Daithankar
Sent: Wednesday, November 19, 2003 6:23 AM
To: Uros
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Optimizing query
Uros wrote:
> Hello!
>
> I have some trouble getting good results from my query.
>
> here is structure
>
> stat_views
> id | integer
> id_zone | integer
> created | timestamp
>
>
> I have btree index on created and also id and there is 1633832 records in
> that table
>
> First of all I have to manualy set seq_scan to OFF because I always get
> seq_scan. When i set it to off my explain show:
>
> explain SELECT count(*) as views FROM stat_views WHERE id = 12;
> QUERY PLAN
>
----------------------------------------------------------------------------
------------------------
> Aggregate (cost=122734.86..122734.86 rows=1 width=0)
> -> Index Scan using stat_views_id_idx on stat_views
(cost=0.00..122632.60 rows=40904 width=0)
> Index Cond: (id = 12)
>
> But what I need is to count views for some day, so I use
>
> explain SELECT count(*) as views FROM stat_views WHERE date_part('day',
created) = 18;
>
> QUERY PLAN
>
----------------------------------------------------------------------------
--------
> Aggregate (cost=100101618.08..100101618.08 rows=1 width=0)
> -> Seq Scan on stat_views (cost=100000000.00..100101565.62 rows=20984
width=0)
> Filter: (date_part('day'::text, created) = 18::double precision)
>
>
> How can I make this to use index and speed the query. Now it takes about
12
> seconds.
Can you post explain analyze for the same?
Shridhar
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Rob Sell wrote: > Greetings all, > > Yesterday I upgraded from 7.3 to 7.4 now psql doesn't work! I get the > following error. > > psql: relocation error: psql: undefined symbol: get_progname > > Any ideas out there? You have an old copy of the library or binaries around somewhere. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073