query execution time

Hi!

This mailing list is for GSoC students to discuss their proposals.

Please direct your question to pgsql-general@postgresql.org.

-selena

On Sun, Mar 20, 2011 at 9:59 PM, preetika tyagi <preetikatyagi@gmail.com> wrote:

Hi,
I am wondering if someone can help me understand the following query
execution behavior:
I have two similar tables and table2 consists of 5000000 records.
Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: A ms.
Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: B ms.
Sometimes B >> A (B is very larger than A). Both queries are same and run
twice. What could be the reason that the same query is taking very long
sometime?
Thanks a lot!

--
http://chesnok.com

Hi,

I am wondering if someone can help me understand the following query
execution behavior:

I have two similar tables and table2 consists of 5000000 records.

Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: A ms.

Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: B ms.

Sometimes B >> A (B is very larger than A). Both queries are same and run
twice. What could be the reason that the same query is taking very long
sometime?

Thanks a lot!

On Mar 21, 2011, at 10:42 AM, preetika tyagi wrote:

Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: A ms.

Query: INSERT INTO table1 SELECT * FROM table2.
Execution Time: B ms

If session is same, then
B < A if the data is cached due to execution of first statement.
B > A, if the there is any change happened Or Any session has evicted the cached data.

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
The Enterprise PostgreSQL Company
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

Hi!

in the GSOC wiki, there is a potential project idea mentioning "Write Foreign Data Wrappers
for several external data sources (ODBC, SQL Server, Oracle, MySQL, CouchDB, Redis, etc.)"

Can anyone help explain how these wrapper are going to be like?

Thanks!

ZHENG Yang

Thank you, Vibhor!

I am wondering if caching will make such a big difference. For example, if A
is 15 minutes, then B is 1.5 hrs.
What could be other factors in case database server is separated from
clients (on different computer systems)?

Preetika

On Mon, Mar 21, 2011 at 2:48 AM, Vibhor Kumar <vibhor.kumar@enterprisedb.com

On Mar 21, 2011, at 9:55 AM, preetika tyagi wrote:

For example, if A is 15 minutes, then B is 1.5 hrs.

Well, considering that random disk access is on the order of 10,000 times slower than RAM...

But you can answer the question yourself by comparing the query run against cold caches (after a reboot, or various command-line tricks to purge cache) vs against warm caches (twice back-to-back).

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Hi!

On Mon, Mar 21, 2011 at 5:17 AM, Zheng Yang <zhengyang4k@gmail.com> wrote:

in the GSOC wiki, there is a potential project idea mentioning "Write
Foreign Data Wrappers
 for several external data sources (ODBC, SQL Server, Oracle, MySQL,
CouchDB, Redis, etc.)"
Can anyone help explain how these wrapper are going to be like?

Have a look at http://wiki.postgresql.org/images/4/4c/SQLMED-FOSDEM2009.pdf
for starters. See if you can review that and come back with more
specific questions.

There are a few community members interested in seeing this move
forward, so this project is likely a great one for GSoC.

My suggestion would be to pick just one or two for this summer's project.

-selena

--
http://chesnok.com

Thank you, Scott!
I tried running the same query after reboot and back-to-back, it was taking
less time in both the cases. It means the problem is something else.

Can there be a reason which is more hardware/operating system specific and
due to which the behavior is not uniform?

Preetika

On Mon, Mar 21, 2011 at 9:06 AM, Scott Ribe <scott_ribe@elevated-dev.com>wrote:

On 03/21/2011 12:57 PM, Selena Deckelmann wrote:

Hi!

On Mon, Mar 21, 2011 at 5:17 AM, Zheng Yang<zhengyang4k@gmail.com> wrote:

in the GSOC wiki, there is a potential project idea mentioning "Write
Foreign Data Wrappers
for several external data sources (ODBC, SQL Server, Oracle, MySQL,
CouchDB, Redis, etc.)"
Can anyone help explain how these wrapper are going to be like?

Have a look at http://wiki.postgresql.org/images/4/4c/SQLMED-FOSDEM2009.pdf
for starters. See if you can review that and come back with more
specific questions.

There are a few community members interested in seeing this move
forward, so this project is likely a great one for GSoC.

My suggestion would be to pick just one or two for this summer's project.

Yes, but 9.1 *has* actual FDWs. The best thing to do (other than
attending my talk on Thursday :-) ) is to look at the file_fdw module in
git head, to see an actual working example.

cheers

andrew

On Mon, Mar 21, 2011 at 11:45 AM, Andrew Dunstan <andrew@dunslane.net> wrote:

Yes, but 9.1 *has* actual FDWs. The best thing to do (other than attending
my talk on Thursday :-) )

:) I'll be around, so hopefully I can attend your talk and post up some notes!

is to look at the file_fdw module in git head, to
see an actual working example.

I figured if I posted some wrong information someone would respond! :)

Zheng - here is a link to the existing code:
http://git.postgresql.org/gitweb?p=postgresql.git;a=tree;f=contrib/file_fdw

-selena

--
http://chesnok.com

On Mar 21, 2011, at 12:03 PM, preetika tyagi wrote:

I tried running the same query after reboot and back-to-back, it was taking less time in both the cases. It means the problem is something else.

Can there be a reason which is more hardware/operating system specific and due to which the behavior is not uniform?

While I do have a couple of ideas, you're probably better served by letting those here with more optimization experience help you, as their answers will be more complete.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice

Thanks! I will take a look!

ZY

On 22-Mar-2011, at 3:49 AM, Selena Deckelmann wrote:

Hi guys,

I've figured out what a FDW does. It's quite clear from the doc that a FDW is an interface postgresql database used to communicate with
other data sources either it be a file, another DBMS or even weblinks (please correct me if i am wrong).

I have a few more specific questions on FDW. hope you guys can help!

1) by right the FDW should be implemented by the vendor of the datasource, is that correct?

2) what are the FDW that has been implemented? I've seen an plain file FWD module from the link provided by selena.

3) in one of the examples, when declaring an FDW, "..LIBRARY ’foosql_fdw.so’ LANGUAGE C;" the LANGUAGE option is going to be C only?

4) Is theFDW something similar to the Mysql storage engine architecture?

5) for the datalink, the doc has mentioned about it's going to be OS dependent. but i dun quite understand how does the kernel module and LD_PRELOAD will work in this case, esp.LD_PRELOAD.

That's the question so far. Thanks very much!

Regards,
Zheng Yang

On 22-Mar-2011, at 3:49 AM, Selena Deckelmann wrote:

Hi,

Le 24/03/2011 06:39, Zheng Yang a �crit :

[...]
I've figured out what a FDW does. It's quite clear from the doc that a FDW is an interface postgresql database used to communicate with
other data sources either it be a file, another DBMS or even weblinks (please correct me if i am wrong).

You're right.

I have a few more specific questions on FDW. hope you guys can help!

1) by right the FDW should be implemented by the vendor of the datasource, is that correct?

If you mean that an Oracle FDW should be implemented by Oracle, and an
Excel FDW should be implemented by Microsoft, then no, I don't think so.
AFAICS, anyone can write any FDW.

2) what are the FDW that has been implemented? I've seen an plain file FWD module from the link provided by selena.

Yeah, that's the one provided as a contrib module. I've also seen a
postgres one and a twitter one on pgsql-hackers. Andrew wrote two other
FDWs he'll talk about today at PGEast
(http://people.planetpostgresql.org/andrew/index.php?/archives/163-First-extension.html).
I hope he'll publish his slides somewhere really soon :)

3) in one of the examples, when declaring an FDW, "..LIBRARY �foosql_fdw.so� LANGUAGE C;" the LANGUAGE option is going to be C only?

According to
http://developer.postgresql.org/pgdocs/postgres/fdwhandler.html, yes.

4) Is theFDW something similar to the Mysql storage engine architecture?

Not really.

5) for the datalink, the doc has mentioned about it's going to be OS dependent. but i dun quite understand how does the kernel module and LD_PRELOAD will work in this case, esp.LD_PRELOAD.

No idea.

Writing some FDWs would make a really good GSoC project.

--
Guillaume
http://www.postgresql.fr
http://dalibo.com

Hi Guillaume,

Thanks very much! Your answers help a lot!

Le 24/03/2011 06:39, Zheng Yang a écrit :

4) Is theFDW something similar to the Mysql storage engine architecture?

Not really.

AFAIK,there is a CSV storage engine in MySql that serves as a wrapper, enable executing SQL queries directly on the file.
This makes me think that the CSV FDW was doing similar things.

5) for the datalink, the doc has mentioned about it's going to be OS dependent. but i dun quite understand how does the kernel module and LD_PRELOAD will work in this case, esp.LD_PRELOAD.

No idea.

I am quite new to postgresql, but slowly picking up :) In general context, storing images directly inside DBMS fields as BLOBs was not considered as a good practice.
However, storing file directories or links may cause inconsistency. So I am quite interested in how this datalink concept can be implemented!

Writing some FDWs would make a really good GSoC project.

Haha, agree! I believe that's also a great starting point for learning pgsql internals.

cheers

ZY

Excerpts from Zheng Yang's message of jue mar 24 05:49:19 -0300 2011:

I am quite new to postgresql, but slowly picking up :) In general context, storing images directly inside DBMS fields as BLOBs was not considered as a good practice.
However, storing file directories or links may cause inconsistency. So I am quite interested in how this datalink concept can be implemented!

I'm not really sure how different would be handling the file linking in
an FDW. For example, if a transaction runs that deletes a file through
the FDW, and the transaction rolls back, how are you going to restore
the file to life?

(It sounds like you're trying to have a FDW that would present a
directory as a table, and each file in the dir as a row. Maybe it's not
a bad idea but it needs a lot more thought.)

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Thu, March 24, 2011 12:49 pm, Alvaro Herrera wrote:

Excerpts from Zheng Yang's message of jue mar 24 05:49:19 -0300 2011:

I am quite new to postgresql, but slowly picking up :) In general
context, storing images directly inside DBMS fields as BLOBs was not
considered as a good practice.
However, storing file directories or links may cause inconsistency. So
I am quite interested in how this datalink concept can be implemented!

I'm not really sure how different would be handling the file linking in
an FDW. For example, if a transaction runs that deletes a file through
the FDW, and the transaction rolls back, how are you going to restore
the file to life?

(It sounds like you're trying to have a FDW that would present a
directory as a table, and each file in the dir as a row. Maybe it's not
a bad idea but it needs a lot more thought.)

Currently FDWs can't store anything. They are read-only.

But I don't really buy this stuff about not storing images in the
database. I've done numbers of apps that do exactly that with great
success. If the images are huge that's another matter, but for small
images it works just fine.

cheers

andrew

And, Andrew's slides from his talk today are now available:
http://people.planetpostgresql.org/andrew/uploads/fdw2.pdf

We really think the FDW projects would be great ones for GSoC. I know
we said that already. :)

-selena

Thank you for the slides. And yes FDW seems like a good project to get
involved in especially if one (and by one I mean me) is knew to the Postgres
codebase.
Regards,
Shiv

On Fri, Mar 25, 2011 at 4:59 AM, Selena Deckelmann <selena@chesnok.com>wrote: