| 00:41.15 | *** join/#uphpu UtahDave (~boucha@c-174-52-220-233.hsd1.ut.comcast.net) |
| 00:41.16 | *** mode/#UPHPU [+o UtahDave] by ChanServ |
| 00:45.02 | *** join/#uphpu clintberry (~clintberr@c-76-27-26-207.hsd1.ut.comcast.net) |
| 00:45.03 | *** mode/#UPHPU [+v clintberry] by ChanServ |
| 01:01.55 | *** join/#uphpu ninnypan_ (~ninnypant@unaffiliated/ninnypants) |
| 01:01.55 | *** mode/#UPHPU [+v ninnypan_] by ChanServ |
| 02:47.52 | *** join/#uphpu HumanCell (~Humancell@207.224.214.130) |
| 02:52.33 | *** join/#uphpu jtsnow (~jsnow@75-162-236-198.slkc.qwest.net) |
| 02:52.33 | *** mode/#UPHPU [+v jtsnow] by ChanServ |
| 03:02.41 | *** join/#uphpu ninnypan_ (~ninnypant@unaffiliated/ninnypants) |
| 03:02.41 | *** mode/#UPHPU [+v ninnypan_] by ChanServ |
| 04:12.22 | *** join/#uphpu clintberry (~clintberr@c-76-27-26-207.hsd1.ut.comcast.net) |
| 04:12.26 | *** mode/#UPHPU [+v clintberry] by ChanServ |
| 06:30.33 | *** join/#uphpu jshultz (~jshultz@107.0.84.214) |
| 07:04.32 | *** join/#uphpu ninnypan_ (~ninnypant@unaffiliated/ninnypants) |
| 07:04.33 | *** mode/#UPHPU [+v ninnypan_] by ChanServ |
| 08:27.01 | *** join/#uphpu carmony (~carmony@162.243.52.69) |
| 08:27.01 | *** mode/#UPHPU [+o carmony] by ChanServ |
| 09:05.05 | *** join/#uphpu ninnypan_ (~ninnypant@unaffiliated/ninnypants) |
| 09:05.06 | *** mode/#UPHPU [+v ninnypan_] by ChanServ |
| 09:41.41 | *** join/#uphpu jtsnow (~jsnow@75-162-236-198.slkc.qwest.net) |
| 09:41.41 | *** mode/#UPHPU [+v jtsnow] by ChanServ |
| 11:06.02 | *** join/#uphpu ninnypan_ (~ninnypant@unaffiliated/ninnypants) |
| 11:06.03 | *** mode/#UPHPU [+v ninnypan_] by ChanServ |
| 13:06.53 | *** join/#uphpu ninnypan_ (~ninnypant@unaffiliated/ninnypants) |
| 13:06.53 | *** mode/#UPHPU [+v ninnypan_] by ChanServ |
| 14:08.59 | *** join/#uphpu macnewbold (~macnewbol@66.219.204.99.provo.dynamic.broadweavenetworks.net) |
| 14:09.00 | *** mode/#UPHPU [+o macnewbold] by ChanServ |
| 14:18.22 | *** join/#uphpu opapo (~quassel@204.113.97.235) |
| 14:18.22 | *** mode/#UPHPU [+v opapo] by ChanServ |
| 14:23.01 | *** join/#uphpu Benni_Hanna (~Benni_Han@neutroninteractive-xmission.xmission.com) |
| 14:26.50 | *** join/#uphpu ninnypants (~ninnypant@unaffiliated/ninnypants) |
| 14:26.52 | *** mode/#UPHPU [+v ninnypants] by ChanServ |
| 14:33.24 | *** join/#uphpu MikeHansenMe (~mike@2601:7:6fc0:1d:28f2:4de8:4f6e:b9a7) |
| 14:54.19 | *** join/#uphpu kendsnyder (~kendsnyde@67.214.245.39) |
| 14:56.17 | *** join/#uphpu kendsnyder (~kendsnyde@67.214.245.39) |
| 14:56.17 | *** mode/#UPHPU [+v kendsnyder] by ChanServ |
| 14:57.27 | *** join/#uphpu eggyknap (~jtolley@184-11-47-86.dr01.dlta.ut.frontiernet.net) |
| 14:57.30 | *** join/#uphpu eggyknap (~jtolley@unaffiliated/eggyknap) |
| 14:57.30 | *** mode/#UPHPU [+v eggyknap] by ChanServ |
| 14:57.51 | *** join/#uphpu SunSparc (~jonathan@69-27-175-74.acedatacenter.com) |
| 14:57.51 | *** mode/#UPHPU [+v SunSparc] by ChanServ |
| 15:01.01 | *** join/#uphpu mgeary (~mgeary@APuteaux-651-1-108-206.w82-124.abo.wanadoo.fr) |
| 15:01.01 | *** mode/#UPHPU [+v mgeary] by ChanServ |
| 15:01.32 | SunSparc | Wilcommen mgeary. |
| 15:01.41 | mgeary | heya |
| 15:07.46 | *** join/#uphpu ninnypan_ (~ninnypant@unaffiliated/ninnypants) |
| 15:07.46 | *** mode/#UPHPU [+v ninnypan_] by ChanServ |
| 15:10.02 | *** join/#uphpu clintberry (~clintberr@75-148-102-17-Utah.hfc.comcastbusiness.net) |
| 15:10.02 | *** mode/#UPHPU [+v clintberry] by ChanServ |
| 15:19.38 | *** join/#uphpu macnewbold1 (~macnewbol@66.219.204.98.provo.dynamic.broadweavenetworks.net) |
| 15:19.39 | *** mode/#UPHPU [+o macnewbold1] by ChanServ |
| 15:30.28 | *** join/#uphpu undertakingyou (~undertaki@neutroninteractive-xmission.xmission.com) |
| 15:30.28 | *** mode/#UPHPU [+v undertakingyou] by ChanServ |
| 15:38.57 | carmony | hola :) |
| 15:39.29 | fungus | aloh |
| 15:39.50 | fungus | (: |
| 15:40.21 | peno | sugnuf si sdrawkcab |
| 15:40.32 | herlo | heh |
| 15:41.33 | *** join/#uphpu itimber (~Adium@ip72-209-186-65.ks.ks.cox.net) |
| 15:41.34 | *** mode/#UPHPU [+v itimber] by ChanServ |
| 15:49.36 | *** join/#uphpu ninnypan_ (~ninnypant@unaffiliated/ninnypants) |
| 15:49.37 | *** mode/#UPHPU [+v ninnypan_] by ChanServ |
| 15:52.05 | fungus | heh |
| 15:52.20 | fungus | backwards is fungus |
| 15:56.47 | *** join/#uphpu hallamigo (~hallamigo@75-145-59-129-Utah.hfc.comcastbusiness.net) |
| 16:03.36 | *** join/#uphpu josephscott (~josephsco@proxy.iad.automattic.com) |
| 16:16.47 | carmony | .lol |
| 16:16.52 | carmony | ~lolstats |
| 16:16.52 | infobot | total count of lol on #uphpu: 5718. Top 3: TuxToaster -- 1173 (20.5%), herlo -- 669 (11.7%), SunSparc -- 627 (11.0%) |
| 16:16.58 | carmony | ~lolstats carmony |
| 16:16.58 | infobot | carmony has said lol 578 times (10.1 %), ranked 4/133 (percentile: 3.0 %) |
| 16:17.08 | carmony | I have a decent pad |
| 16:17.15 | carmony | some breathing room before I get to #3 |
| 16:17.28 | herlo | lol |
| 16:17.45 | herlo | it's getting close between me and SunSparc though |
| 16:17.47 | carmony | thats 670 herlo! |
| 16:17.49 | herlo | lol |
| 16:17.50 | carmony | yeah |
| 16:21.14 | *** join/#uphpu benbankes (~ben@50-200-100-242-static.hfc.comcastbusiness.net) |
| 16:28.15 | josephscott | that tuxtoaster must be very jolly :-) |
| 16:28.36 | herlo | indeed |
| 17:15.05 | *** join/#uphpu UtahDave (~boucha@63.230.12.160) |
| 17:15.06 | *** mode/#UPHPU [+o UtahDave] by ChanServ |
| 17:35.05 | unum | can someone walk me through indexing strategies |
| 17:55.16 | fungus | In the beginning there was the card catalog... |
| 18:21.12 | *** join/#uphpu undertak_ (~undertaki@neutroninteractive-xmission.xmission.com) |
| 18:24.09 | jsmith | ~lolstats jsmith |
| 18:24.09 | infobot | jsmith has said lol 0 times (0.0 %) |
| 18:24.14 | jsmith | How 'bout that :-) |
| 18:24.37 | jsmith | unum: Indexing strategies on a relational database? |
| 18:28.23 | *** join/#uphpu MikeHansenMe (~mike@173-165-131-122-utah.hfc.comcastbusiness.net) |
| 18:46.23 | SunSparc | ~seen TuxToaster |
| 18:46.26 | infobot | tuxtoaster <~TuxToaste@66.133.98.27> was last seen on IRC in channel #uphpu, 11d 1h 54m 20s ago, saying: 'oh I missed you saying when you switch to Windows'. |
| 18:53.22 | unum | jsmith: yes |
| 18:53.26 | *** join/#uphpu josephscott (~josephsco@proxy.iad.automattic.com) |
| 18:53.50 | jsmith | unum: OK, I'll bite -- if you've got a minute now |
| 18:55.41 | unum | I'll post the same here as I did it udbug since there's only two other people in there |
| 18:55.47 | unum | I have a postgresql database |
| 18:55.52 | unum | I have a group of text responses |
| 18:55.58 | unum | that are linked to questions |
| 18:56.04 | unum | and the are reviewed twice then exported |
| 18:56.06 | jsmith | unum: In general, you can think of an index as a way of trading disk space (to store the metadata) and time (the time to write additional metadata about the record being inserted or updated), in an effort to speed up SELECTs (because the database can use the metadata to more easily find the record) |
| 18:56.10 | unum | they are reviewed as a question |
| 18:56.16 | unum | so I'll query for one that's linked to a certain question and hasn't been reviewed yet |
| 18:56.33 | jsmith | OK, when you say "group of text responses", they're in one table? |
| 18:56.39 | jsmith | and the questions are in another? |
| 18:56.49 | unum | yes I have 5 million text responses in one table |
| 18:56.51 | jsmith | And you use a JOIN to find one that hasn't been reviewed? |
| 18:57.02 | unum | yes |
| 18:57.04 | jsmith | OK, perfect example :-) |
| 18:57.30 | jsmith | Without an index, a database would typically have to scan the entire list of records to find the one you were looking for |
| 18:57.58 | jsmith | By adding one or more indices, you can easily help the database find the appropriate records more easily |
| 18:58.26 | jsmith | Now, when you run your query, what columns does it key off of? Question_id, response_id, and reviewed (something like that?) |
| 18:58.47 | jsmith | I'm guessing that Question_id and response_id are already primary keys, so they should have an implicit index |
| 19:00.44 | unum | ya they are primary keys on their respective tables |
| 19:00.56 | unum | sorry, a boss walked in |
| 19:01.04 | unum | had to update something for him |
| 19:01.12 | jsmith | No worries |
| 19:02.21 | unum | so I've set up and index on question_id on the responses tables |
| 19:02.49 | jsmith | You probably want an index on *both* question_id and response_id reviewed |
| 19:03.08 | jsmith | s/*both* question_id and response_id reviewed/*both* question_id and reviewed/ |
| 19:03.27 | unum | by reviewed do you mean clustered? |
| 19:05.34 | jsmith | Whatever the column is that tells you whether or not the responses have been reviewed |
| 19:06.19 | unum | ah ok |
| 19:06.27 | unum | ya that's actually the one I was wondering about |
| 19:06.34 | jsmith | So, first step: Give me the SQL query that you use to select your reviews |
| 19:07.10 | jsmith | Second step: Run the query with EXPLAIN on the front... as in EXPLAIN SELECT foo FROM bar WHERE baz = 'frozznib'; |
| 19:07.20 | jsmith | And then pastebin the result, please :-) |
| 19:12.04 | unum | http://unum5.org/db.txt |
| 19:12.11 | unum | I can do paste bin if that's easier to read |
| 19:12.14 | unum | the query first |
| 19:12.27 | unum | it's kind of complicated because of some recent feature requests |
| 19:12.38 | unum | of course that shows some other good places for indexes.... |
| 19:13.55 | jsmith | See the parts of that output that say "serial scan"? |
| 19:14.09 | jsmith | That's usually a really big red flag that you need an index on that particular column |
| 19:14.48 | jsmith | Looks like your "studies" table might be the one to blame here |
| 19:15.29 | unum | ya |
| 19:15.46 | unum | I don't think I have an index on the sibling_study_index |
| 19:15.48 | unum | when I should |
| 19:15.59 | unum | that's a MUCH smaller table :) |
| 19:18.45 | unum | huh, turns out I didn't have an index on the primary key on that table |
| 19:18.53 | unum | that's the most important table... |
| 19:19.51 | jsmith | Doh! |
| 19:20.29 | unum | well thanks for your help |
| 19:20.34 | unum | that makes a lot of sense |
| 19:20.41 | unum | I think I've been doing it mostly right :) |
| 19:20.49 | jsmith | :-) |
| 19:20.51 | unum | now I know how to make it better |
| 19:20.55 | unum | I have plenty of disk space |
| 19:21.13 | unum | now do I need to run cluster on these indexes to make the best use of them? |
| 19:21.23 | unum | or only after I first create them |
| 20:29.47 | *** join/#uphpu TuxToaster (~TuxToaste@wfw.vivint.com) |
| 20:29.48 | *** mode/#UPHPU [+v TuxToaster] by ChanServ |
| 20:46.48 | *** join/#uphpu KingMichael (~KingMicha@c-50-160-126-35.hsd1.ut.comcast.net) |
| 20:59.22 | TuxToaster | ~rlart |
| 20:59.22 | infobot | squishes tierra like a bug |
| 21:02.19 | josephscott | unum: http://www.postgresql.org/docs/9.1/static/sql-cluster.html |
| 21:04.50 | carmony | ~lart josephscott |
| 21:04.50 | infobot | duct-tapes josephscott to the floor and drools on him |
| 21:14.28 | *** join/#uphpu skinner (~david@72.250.214.16) |
| 21:14.28 | *** join/#uphpu djedi (~djedi@redseam.info) |
| 21:14.28 | *** mode/#UPHPU [+v skinner] by barjavel.freenode.net |
| 21:16.20 | *** join/#uphpu MikeHansenMe (~mike@2601:7:6fc0:1d:28f2:4de8:4f6e:b9a7) |
| 22:15.13 | *** join/#uphpu ninnypants (~ninnypant@unaffiliated/ninnypants) |
| 22:15.14 | *** mode/#UPHPU [+v ninnypants] by ChanServ |
| 23:51.20 | *** join/#uphpu xuu_ (~xuu@hsc102-233.med.utah.edu) |
| 23:51.25 | *** join/#uphpu romanovic_ (~romanovic@puritynine.com) |
| 23:53.38 | *** join/#uphpu linagee_ (~linagee@indigeotech.com) |
| 23:53.40 | *** join/#uphpu linagee_ (~linagee@about/linux/regular/linagee) |