Thread: query execution time
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!
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!
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
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 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:
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 attendingmy 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, tosee 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
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
Regards,
Shiv
On Fri, Mar 25, 2011 at 4:59 AM, Selena Deckelmann <selena@chesnok.com> wrote:
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
--
Sent via pgsql-students mailing list (pgsql-students@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-students
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:
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?
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?
And can anyone help explain what a planner is? What does this "provide cost estimates to planner" mean? Thanks!
Hi Shiv, I am also from National University of Singapore!
cheers,
ZY
On 25-Mar-2011, at 8:53 AM, Shiv wrote:
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,
ShivOn Fri, Mar 25, 2011 at 4:59 AM, Selena Deckelmann <selena@chesnok.com> wrote: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
--
Sent via pgsql-students mailing list (pgsql-students@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-students
Haha.. that's great! Geographical proximity is always nice!
The Flickr example is a good question. I would be interested to know if that is allowable (under the specifications for FDW)
Regards,
Shiv
Regards,
Shiv
On Fri, Mar 25, 2011 at 10:07 AM, 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: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?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?And can anyone help explain what a planner is? What does this "provide cost estimates to planner" mean? Thanks!Hi Shiv, I am also from National University of Singapore!cheers,ZYOn 25-Mar-2011, at 8:53 AM, Shiv wrote: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,
ShivOn Fri, Mar 25, 2011 at 4:59 AM, Selena Deckelmann <selena@chesnok.com> wrote: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
--
Sent via pgsql-students mailing list (pgsql-students@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-students
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
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:
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
ZY
On 25-Mar-2011, at 11:38 PM, Selena Deckelmann wrote:
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, thedatasource 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 understandingcorrect?
Yes, the first ten results.I've briefly gone through the slides. Regarding the 6 callbacks, is thatcorrect to say that a full table scan will always be performed irregardlessof 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 costestimates 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
Hey Zheng,
I don't seem to be having any issues. Something else might be going wrong.
Regards,
Shiv
Regards,
Shiv
On Sat, Mar 26, 2011 at 8:46 PM, Zheng Yang <zhengyang4k@gmail.com> wrote:
Hi,It seems hanging there forever.
On 26-Mar-2011, at 4:11 PM, Dave Page wrote:
>
> Try cloning over http:
>
> git clone http://git.postgresql.org/git/postgresql.git
>
$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
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/' folder and 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?
ZY
On 26-Mar-2011, at 11:07 PM, Andrew Dunstan wrote:
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 Andrew and Guillaume,
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, isthat correct to say that a full table scan will always be performedirregardless 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 alimit to the foreign side, possibly via table options. I'm fairlysure you won't be able to get it via the SELECT statement.Regarding the previous flickr example, I'm wondering how this 'freetext search' function can be done if the FDW is blind to the SELECTstatement.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 webservice and return the next 'row' .The problem is that there are a huge number of photos in flickrserver 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 needto embed this sort of stuff in table options.so you would have something like:create foreign table panda_flickr (photo bytea, ...)server flickr_serveroptions (searchterm 'panda', maxrows '50');select photo from panda_flickr;This would work but means you need to create a new foreign table tosearch 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.
I think this is a common issue for all FDWs that need to access remote resources over a network. For example, if there were a Mysql FDW, a full table scan implies the whole table will be transferred over.
it is not quite efficient for large tables.
If a table size is 1GB, iterating the whole table row by row means those 1GB of data needs to be transferred over. This may take hours even if for an sql statement as simple as
SELECT * from table where id = 1;
cheers,
ZY
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