Обсуждение: 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!

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

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

Thanks! I will take a look!


ZY

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

> 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

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
tocommunicate 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
goingto 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
thekernel 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
thekernel 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
asBLOBs 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
conceptcan 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
fieldsas 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
conceptcan 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

Hi!

On Thu, Mar 24, 2011 at 7:07 PM, Zheng Yang <zhengyang4k@gmail.com> wrote:
> Thanks for the slides! As what's mentioned on one of the slides, the
> datasource of FDW can be virtually everything.
> So if there were a FDW for flickr API, the examples will be something like:
> 1) CREATE FOREIGN TABLE flickr_table (photo_id INT, ownerid INT, photo BLOB,
> text TEXT) SERVER flick_fdw OPTIONS (api_key 'AAAA');
> p.s. the text field is used as the keyword for free text search.
> when query from the table:

Yes, but perhaps we could name the 'text' column something like
'search' or ... someone else could help come up with a better name. :)

> 2) SELECT photo FROM flickr_table WHERE text = 'panda' LIMIT 0, 10;
> This will return top ten photos relevant to 'panda'. Is my understanding
> correct?

Yes, the first ten results.

> I've briefly gone through the slides. Regarding the 6 callbacks, is that
> correct to say that a full table scan will always be performed irregardless
> of the sql statement,
> the FDW is blind to the sql query performed, right?

That's correct.

> And can anyone help explain what a planner is? What does this "provide cost
> estimates to planner" mean? Thanks!

Have a look at this for a quick overview:
http://www.postgresql.org/docs/9.0/static/planner-optimizer.html

-selena

--
http://chesnok.com

On Sat, Mar 26, 2011 at 2:23 AM, Zheng Yang <zhengyang4k@gmail.com> wrote:
> Thanks!
> Can anyone help on this:
> I wanted to check out an copy of pgsql source so that I can start playing
> around with it.
> I followed the working with git instructions here:
> http://wiki.postgresql.org/wiki/Working_with_Git
> but when I run this command:
>
> git clone git://git.postgresql.org/git/postgresql.git
>
> I get this error:
> Cloning into gitpostgresql...
> fatal: The remote end hung up unexpectedly
>

Try cloning over http:

git clone http://git.postgresql.org/git/postgresql.git


--
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Hi,

On 26-Mar-2011, at 4:11 PM, Dave Page wrote:
>
> Try cloning over http:
>
> git clone http://git.postgresql.org/git/postgresql.git
>

It seems hanging there forever.

$git clone http://git.postgresql.org/git/postgresql.git
Cloning into postgresql...


ZY

On 03/24/2011 10:07 PM, Zheng Yang wrote:
>
>
> I've briefly gone through the slides. Regarding the 6 callbacks, is
> that correct to say that a full table scan will always be performed
> irregardless of the sql statement,
> the FDW is blind to the sql query performed, right?

Yes, fairly much. If the feed is large you need some way to pass a limit
to the foreign side, possibly via table options. I'm fairly sure you
won't be able to get it via the SELECT statement.

>
> And can anyone help explain what a planner is? What does this "provide
> cost estimates to planner" mean? Thanks!


See <http://www.postgresql.org/docs/current/static/planner-optimizer.html>

If you're going to work on PostgreSQL code you need to read the docs.

cheers

andrew

Le 27/03/2011 14:51, Zheng Yang a écrit :
> Hi,
>
> I managed to clone a copy of 9.1devel and compiled an binary from it. I also did a make in the 'contrib/file_fdw/'
folderand a file_fdw.so file is generated. 
>
> But the following error occurs when I was trying to experiment with the file FDW:
>
> postgres=# CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
> ERROR:  could not access file "file_fdw": No such file or directory
> STATEMENT:  CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
> ERROR:  could not access file "file_fdw": No such file or directory
>
>
> Did I miss anything?
>

make install?


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

Haha.. Thanks, Guilaume. Forgot to install. It works now!

ZY

On 28-Mar-2011, at 3:28 PM, Guillaume Lelarge wrote:

> Le 27/03/2011 14:51, Zheng Yang a écrit :
>> Hi,
>>
>> I managed to clone a copy of 9.1devel and compiled an binary from it. I also did a make in the 'contrib/file_fdw/'
folderand a file_fdw.so file is generated. 
>>
>> But the following error occurs when I was trying to experiment with the file FDW:
>>
>> postgres=# CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
>> ERROR:  could not access file "file_fdw": No such file or directory
>> STATEMENT:  CREATE FUNCTION file_fdw_handler() RETURNS fdw_handler AS 'file_fdw' LANGUAGE C STRICT;
>> ERROR:  could not access file "file_fdw": No such file or directory
>>
>>
>> Did I miss anything?
>>
>
> make install?
>
>
> --
> Guillaume
> http://www.postgresql.fr
> http://dalibo.com

Hi guys,

>>
>> I've briefly gone through the slides. Regarding the 6 callbacks, is that correct to say that a full table scan will
alwaysbe performed irregardless of the sql statement, 
>> the FDW is blind to the sql query performed, right?
>
> Yes, fairly much. If the feed is large you need some way to pass a limit to the foreign side, possibly via table
options.I'm fairly sure you won't be able to get it via the SELECT statement. 
>


Regarding the previous flickr example, I'm wondering how this 'free text search' function can be done if the FDW is
blindto the SELECT statement. 

For instance, the following query is to retrieve a photo relevant to 'panda':

    SELECT photo FROM flickr_table WHERE search LIKE '%panda%';

In this case, the FDW can only open a connection to flickr web service and return the next 'row' .
The problem is that there are a huge number of photos in flickr server and retrieving them sequentially is not
realistic. 
Any ideas on how this can be done?


ZY

Le 29/03/2011 13:28, Zheng Yang a écrit :
> Hi guys,
>
>>>
>>> I've briefly gone through the slides. Regarding the 6 callbacks, is that correct to say that a full table scan will
alwaysbe performed irregardless of the sql statement, 
>>> the FDW is blind to the sql query performed, right?
>>
>> Yes, fairly much. If the feed is large you need some way to pass a limit to the foreign side, possibly via table
options.I'm fairly sure you won't be able to get it via the SELECT statement. 
>>
>
>
> Regarding the previous flickr example, I'm wondering how this 'free text search' function can be done if the FDW is
blindto the SELECT statement. 
>
> For instance, the following query is to retrieve a photo relevant to 'panda':
>
>     SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>
> In this case, the FDW can only open a connection to flickr web service and return the next 'row' .
> The problem is that there are a huge number of photos in flickr server and retrieving them sequentially is not
realistic. 
> Any ideas on how this can be done?
>

It probably means that flickr is not a good example of a nice fdw.


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

On 03/29/2011 11:48 AM, Guillaume Lelarge wrote:
> Le 29/03/2011 13:28, Zheng Yang a écrit :
>> Hi guys,
>>
>>>> I've briefly gone through the slides. Regarding the 6 callbacks, is that correct to say that a full table scan
willalways be performed irregardless of the sql statement, 
>>>> the FDW is blind to the sql query performed, right?
>>> Yes, fairly much. If the feed is large you need some way to pass a limit to the foreign side, possibly via table
options.I'm fairly sure you won't be able to get it via the SELECT statement. 
>>>
>>
>> Regarding the previous flickr example, I'm wondering how this 'free text search' function can be done if the FDW is
blindto the SELECT statement. 
>>
>> For instance, the following query is to retrieve a photo relevant to 'panda':
>>
>>     SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>>
>> In this case, the FDW can only open a connection to flickr web service and return the next 'row' .
>> The problem is that there are a huge number of photos in flickr server and retrieving them sequentially is not
realistic.
>> Any ideas on how this can be done?
>>
> It probably means that flickr is not a good example of a nice fdw.


Neither of you are being very creative. As I mentioned above, you need
to embed this sort of stuff in table options.

so you would have something like:

    create foreign table panda_flickr (photo bytea, ...)
         server flickr_server
         options (searchterm 'panda', maxrows '50');
    select photo from panda_flickr;


cheers

andrew

Le 29/03/2011 18:32, Andrew Dunstan a écrit :
>
>
> On 03/29/2011 11:48 AM, Guillaume Lelarge wrote:
>> Le 29/03/2011 13:28, Zheng Yang a écrit :
>>> Hi guys,
>>>
>>>>> I've briefly gone through the slides. Regarding the 6 callbacks, is
>>>>> that correct to say that a full table scan will always be performed
>>>>> irregardless of the sql statement,
>>>>> the FDW is blind to the sql query performed, right?
>>>> Yes, fairly much. If the feed is large you need some way to pass a
>>>> limit to the foreign side, possibly via table options. I'm fairly
>>>> sure you won't be able to get it via the SELECT statement.
>>>>
>>>
>>> Regarding the previous flickr example, I'm wondering how this 'free
>>> text search' function can be done if the FDW is blind to the SELECT
>>> statement.
>>>
>>> For instance, the following query is to retrieve a photo relevant to
>>> 'panda':
>>>
>>>     SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>>>
>>> In this case, the FDW can only open a connection to flickr web
>>> service and return the next 'row' .
>>> The problem is that there are a huge number of photos in flickr
>>> server and retrieving them sequentially is not realistic.
>>> Any ideas on how this can be done?
>>>
>> It probably means that flickr is not a good example of a nice fdw.
>
>
> Neither of you are being very creative. As I mentioned above, you need
> to embed this sort of stuff in table options.
>
> so you would have something like:
>
>    create foreign table panda_flickr (photo bytea, ...)
>         server flickr_server
>         options (searchterm 'panda', maxrows '50');
>    select photo from panda_flickr;
>

This would work but means you need to create a new foreign table to
search something else.

So, yeah, it works, but it's not convenient.


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

On 03/29/2011 12:35 PM, Guillaume Lelarge wrote:
> Le 29/03/2011 18:32, Andrew Dunstan a écrit :
>>
>> On 03/29/2011 11:48 AM, Guillaume Lelarge wrote:
>>> Le 29/03/2011 13:28, Zheng Yang a écrit :
>>>> Hi guys,
>>>>
>>>>>> I've briefly gone through the slides. Regarding the 6 callbacks, is
>>>>>> that correct to say that a full table scan will always be performed
>>>>>> irregardless of the sql statement,
>>>>>> the FDW is blind to the sql query performed, right?
>>>>> Yes, fairly much. If the feed is large you need some way to pass a
>>>>> limit to the foreign side, possibly via table options. I'm fairly
>>>>> sure you won't be able to get it via the SELECT statement.
>>>>>
>>>> Regarding the previous flickr example, I'm wondering how this 'free
>>>> text search' function can be done if the FDW is blind to the SELECT
>>>> statement.
>>>>
>>>> For instance, the following query is to retrieve a photo relevant to
>>>> 'panda':
>>>>
>>>>      SELECT photo FROM flickr_table WHERE search LIKE '%panda%';
>>>>
>>>> In this case, the FDW can only open a connection to flickr web
>>>> service and return the next 'row' .
>>>> The problem is that there are a huge number of photos in flickr
>>>> server and retrieving them sequentially is not realistic.
>>>> Any ideas on how this can be done?
>>>>
>>> It probably means that flickr is not a good example of a nice fdw.
>>
>> Neither of you are being very creative. As I mentioned above, you need
>> to embed this sort of stuff in table options.
>>
>> so you would have something like:
>>
>>     create foreign table panda_flickr (photo bytea, ...)
>>          server flickr_server
>>          options (searchterm 'panda', maxrows '50');
>>     select photo from panda_flickr;
>>
> This would work but means you need to create a new foreign table to
> search something else.
>
> So, yeah, it works, but it's not convenient.

The other possibility is that you can dig down into the ForiegnScanState
object. The FDW routines are passed a ForeignScanState object which
contains a ScanState object which in turn contains a PlanState object
which has a list of quals. You probably need to dig quite a bit further
but that's a start.

cheers

andrew

Hi guys,

What does this 'quantifiable results' mean for proposals? Thanks!

Regards,
ZY

Hi!

On Thu, Mar 31, 2011 at 1:55 AM, Zheng Yang <zhengyang4k@gmail.com> wrote:

> What does this 'quantifiable results' mean for proposals? Thanks!

It means what will the project deliver in terms of a feature, or more
than one feature, that can be evaluated. Like:

* Implement a FDW for Drizzle that is capable of read-only access to
tables with datatypes of int, float, varchar, char and timedate.

etc.

-selena



--
http://chesnok.com