473,320 Members | 1,858 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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:ur**@sir-mag.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #1
6 1974
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;

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: Uros
To: pg***********@postgresql.org
Sent: Wednesday, November 19, 2003 10:41 AM
Subject: [GENERAL] 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:ur**@sir-mag.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@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

Nov 12 '05 #2
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 pe*****@gmx.net
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3
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

Nov 12 '05 #4
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



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #5
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: pg*****************@postgresql.org
[mailto:pg*****************@postgresql.org] On Behalf Of Shridhar Daithankar
Sent: Wednesday, November 19, 2003 6:23 AM
To: Uros
Cc: pg***********@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #6
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
pg***@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: A Future Computer Scientist | last post by:
A question: Is it really important to think about optimizing the native code or optimizing it for P Code? Or does the code you write make a difference?
6
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too...
2
by: Brian | last post by:
In particular, this question goes out to the Microsoft C++ development team. Back in June, Ronald Laeremans posted the following message. Has the optimizing compiler been included with the...
4
by: Got2Go | last post by:
Hello Group, I have a table that has millions of records in it. About 100 records are added every 5 minutes (one per OIDID) (the sample provided below has data for 2 OIDIDs (99 and 100) And I...
2
by: Nidhi | last post by:
Hello, In my stored procedure i m using same view 5 times. Will this calculate and retrieve from views 5 times ??? Is there any way we can optimise my stored procedure Regards Nidhi
1
by: xpcer | last post by:
hi, friends, i have an problem, like this, i have tables, when i want to use "select" statement that include "join" sintaxt, my query will execute about 1 hour, so long. can u tell me how to...
0
by: rashmigaikwad | last post by:
Hi All, I need help in optimizing the query mentioned below: SELECT SUM(CASE WHEN PROD_TYP='HBRMC' AND INC_MULTIPLE < 2.50 AND LTV <= 0.75 OR LTV IS NULL THEN ADV_IN_QTR ELSE 0 END) ...
5
by: Merennulli | last post by:
To start with, I'll give a simplified overview of my data. BaseRecord (4mil rows, 25k in each Region) ID | Name | Region | etc OtherData (7.5mil rows, 1 or 2 per ID) ID | Type(1/2) | Data ...
5
by: John Rivers | last post by:
Hello has anybody else noticed I have queries that SQL 2000 optimizes correctly and they run very fast Yet SQL 2005 keeps using a dumb query plan and queries run very slow The problem...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.